6 Replies Latest reply: Aug 26, 2018 5:57 PM by Juzer Saifuddin

# Daily running total for each month

Hello,

I would like to have a running total on daily basis for each month.

 2018-01-01 978.29 978.29 2018-01-02 1516.32 2494.61 2018-01-03 1524.51 4019.12 2018-01-04 1633.25 5652.37 2018-01-05 2265.94 7918.31 2018-01-06 1792.14 9710.45 2018-01-07 1983.25 11693.7 2018-01-08 2697.48 14391.2 2018-01-09 2681.05 17072.2 2018-01-10 2584.81 19657 2018-01-11 2519.49 22176.5 2018-01-12 2360.51 24537 2018-01-13 2021.57 26558.6 2018-01-14 2125.68 28684.3 2018-01-15 2528.14 31212.4 2018-01-16 2744.93 33957.4 2018-01-17 2759.99 36717.3 2018-01-18 2672.52 39389.9 2018-01-19 2520.76 41910.6 2018-01-20 2111.12 44021.8 2018-01-21 2326.89 46348.6 2018-01-22 2794.66 49143.3 2018-01-23 2863.11 52006.4 2018-01-24 2707.19 54713.6 2018-01-25 2847.29 57560.9 2018-01-26 2855.96 60416.8 2018-01-27 2350.6 62767.4 2018-01-28 2345.94 65113.4 2018-01-29 2813 67926.4 2018-01-30 3034.92 70961.3 2018-01-31 2743.31 73704.6 2018-02-01 2399.25 2399.25 2018-02-02 1790.3 4189.55 2018-02-03 1482.11 5671.66

As you see that the running total is reset at the start of February.

I want to implement this.

I am currently using this expression which provides me cumulative sum from January to August

rangesum(above(total sum(Spend),0,NoOfDays))

• ###### Re: Daily running total for each month

If the date is your only dimension, just remove the total qualifier from the Above() function.

• ###### Re: Daily running total for each month

Hello Stefan,

Removing total doesn't make much difference. It still gives me accumulated sum from start of January till date.

Can you suggest some other solution?

Thank you.

Juzer

• ###### Re: Daily running total for each month

Sorry, my mistake.

It should work with two dimensions and the removed TOTAL qualifier.

For example, use Monthname(Date) and Date as dimensions.

• ###### Re: Daily running total for each month

Hi Stefan,

I am using a line chart. Can you provide an alternate solution?

Thank you.

• ###### Re: Daily running total for each month

You can use a front-end approach using advanced aggregation, something like

=Sum(

Aggr(

rangesum(above(sum(Spend),0,NoOfDays))

, YearMonth, Date)

)

where YearMonth and Date are fields from your data model and Date needs to be created in chronological order or you need to sort the aggr() dimensions using The sortable Aggr function is finally here!

You can also create a script based solution using an AsOf table like shown here

The As-Of Table

Using a flag for date relations in the same month (in the blog example created simlar to the MonthDiff or YearDiff field), you can reset the accumulated value at Month border.

• ###### Re: Daily running total for each month

Hi Stefan,

Thank you for alternate solutions.

I was able to solve it by implementing it in the load script and using sortable aggr on the front end to reset at the start of each month.

I implemented the following in my script:

if(peek(%MonthKey)=%MonthKey,rangesum(peek(CumulativeSpend),Spend),Spend) as CumulativeSpend

On front-end I used the following expression:

=sum(aggr(max(CumulativeSpend),[%MonthKey],[%DateKey]))

Thank you,