Cumulative Total Formula

[harinder1301]

Hi,

I would like get the total column depending upon the current month of the year .

Please see my below data  :

Year	Month	Score	UpperLimit
2018	Jan	10	24/01/2018
2018	Sep	20	24/01/2018
2019	Jan	20	24/01/2019
2019	May	10	24/01/2019
2019	Oct	10	24/01/2020
2020	Jan	30	24/01/2020
2020	Oct	10	24/01/2020

And I would like to calculate a new column as "Total score" known by at this point in that year. Please see the desired outcome:

Year	Total Desired Outcome	comments
2018	10	Total Outcome known by Jan 2018 is 10
2019	50	Total Outcome known by Jan 2019 is 50
2020	90	Total Outcome known by Jan 2020 is 90

Just wondering if i can achieve it using Multi Row formula?

Appreciate your reply!

Thanks

Harinder

[atcodedog05 · Accepted solution]

Hi @harinder1301 

Here is how you can do it.

Workflow:

Hope this helps : )

[harinder1301]

Thank You, Much appreciated!

[atcodedog05]

Happy to help : ) @harinder1301 

Cheers and have a nice day!

[harinder1301]

Hi

The question I am trying to answer is If today is 24/01/2022 , what was the total score on this day and month in 2018,2019 and 2020?

The workflow is working ok if we have at least one matching month and day(<less than upper limit day/month).

But for the scenarios like this , I am getting 0 records .

Year	Month	Score	UpperLimit	Day*	Month of date	Day of date	RunTot_Score
2018	Feb	10	24/01/2018	20	Jan	24	10
2018	Sep	20	24/01/2018	10	Jan	24	30
2019	Jan	20	24/01/2019	25	Jan	24	50
2019	May	10	24/01/2019	10	Jan	24	60
2019	Oct	10	24/01/2020	2	Jan	24	70
2020	Jan	30	24/01/2020	30	Jan	24	100
2020	Oct	10	24/01/2020	2	Jan	24	110

Instead I want the output like this:

2018: 30

2019:30  //[Total Value from the date < 24th January]

2020:70

Appreciate your advise on this.

Regards

[SoccerTil]

not sure exactly what your calculation needs to be as your output doesn't seem to consistently match your data. does the value for Sep 2018 superceed the value for Jan 2018 when running the total for Jan 2019? If so then your output set should be

10 for Jan 2018

40 for Jan 2019

60 for Jan 2020

If they are individual reports then your output should be

10 for Jan 2018

50 for Jan 2019

100 for Jan 2020

Or am I mistaken? Please clarify.

[harinder1301]

Hi,

Thanks for the reply.

Basically the total score at this point of each year which is (on our before 24 jan of 2018,2019,2020).

Thanks

[SoccerTil]

Then your issue is not just running totals but a complex filter on the data, or a non-equijoin. How are you with macros?

[SoccerTil · Accepted solution]

I saw an issue with your data. If I understand your requirements the data input should be:

row 5 should have upper limit of 24/01/2019 not 24/01/2020. 

Then I can get this results w/o a formated comment

The attached workflow isn't pretty, but it gets the job done with the data you provided. Essentially, records in or before the current month in their year get reported in their own year. Records after the current month in their own year get reported in the next year. Records after the current month in the current year don't get reported. The workflow creates the fields required to summarize by reporting year and then generating the running total.

[harinder1301]

Excellent! Thank you very much.