Cumulative Total Formula

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

Date Time

Data Investigation

Accepted answers

atcodedog05

Hi @harinder1301

Here is how you can do it.

Workflow:

Hope this helps : )

Total Desired Outcome.yxmd

SoccerTil

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.

runningtotal wip.yxmd

All comments

atcodedog05

Hi @harinder1301

Here is how you can do it.

Workflow:

Hope this helps : )

Total Desired Outcome.yxmd

harinder1301

Thank You, Much appreciated!

harinder1301

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

Total Desired Outcome updated.yxmd

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