Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.18 | ||
2018-01-09 | 2681.05 | 17072.23 | ||
2018-01-10 | 2584.81 | 19657.04 | ||
2018-01-11 | 2519.49 | 22176.53 | ||
2018-01-12 | 2360.51 | 24537.04 | ||
2018-01-13 | 2021.57 | 26558.61 | ||
2018-01-14 | 2125.68 | 28684.29 | ||
2018-01-15 | 2528.14 | 31212.43 | ||
2018-01-16 | 2744.93 | 33957.36 | ||
2018-01-17 | 2759.99 | 36717.35 | ||
2018-01-18 | 2672.52 | 39389.87 | ||
2018-01-19 | 2520.76 | 41910.63 | ||
2018-01-20 | 2111.12 | 44021.75 | ||
2018-01-21 | 2326.89 | 46348.64 | ||
2018-01-22 | 2794.66 | 49143.3 | ||
2018-01-23 | 2863.11 | 52006.41 | ||
2018-01-24 | 2707.19 | 54713.6 | ||
2018-01-25 | 2847.29 | 57560.89 | ||
2018-01-26 | 2855.96 | 60416.85 | ||
2018-01-27 | 2350.6 | 62767.45 | ||
2018-01-28 | 2345.94 | 65113.39 | ||
2018-01-29 | 2813 | 67926.39 | ||
2018-01-30 | 3034.92 | 70961.31 | ||
2018-01-31 | 2743.31 | 73704.62 | ||
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))
Looking forward to your response.
Thank you in advance
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,
If the date is your only dimension, just remove the total qualifier from the Above() function.
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
Sorry, my mistake.
It should work with two dimensions and the removed TOTAL qualifier.
For example, use Monthname(Date) and Date as dimensions.
Hi Stefan,
I am using a line chart. Can you provide an alternate solution?
Thank you.
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
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.
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,
HI Swuehl,
I have used your expression to sort out of my problem to get last 2 months sum(sales) excluding current month for each date (dimension value)..
(Sum(Aggr(rangesum(above(sum(Saleable),1)), [Report Date.autoCalendar.YearMonth]))
+ Sum(Aggr(rangesum(above(sum(Saleable),2)), [Report Date.autoCalendar.YearMonth])))
resulting value is correct..But this value representing only for the first day of the month (Feb 01, Mar 01, April 01) only.. Remaining month dates showing Zero (0).like below.. but i need to display last 2 months sales for entire current month(01 to 31st..)
in above table i would like to get same above values for entire month instead of '0's
march all dates -> 65,128 ,Feb all dates-->13,754 . apr -->123,444
can you please let me know how can i achieve this,
@swuehl @jsaifuddin @sunny_talwar