Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need your suggestion so please help,
My scenario is like this,
I am having dataset like,
Month | Sale | Rolling 12 |
Month1 | 3 | |
Month2 | 4 | |
Month3 | 7 | |
Month4 | 4 | |
Month5 | 4 | 22 |
Month6 | 2 | 24 |
Month7 | 1 | 25 |
Month8 | 6 | 31 |
Month9 | 9 | 40 |
Month10 | 10 | 50 |
Month11 | 4 | 54 |
Month12 | 3 | 57 |
Month13 | 2 | 59 |
Month14 | 6 | 65 |
Month15 | 7 | 72 |
Month16 | 2 | 74 |
If my current month is Month16 then I want to show last 12 month data with cumulative sum considering from starting of month(Month1) I need to show data like column3 (Rolling 12) in bar chart.So what formula I should apply.
Thanks,
Vijeta
Hi
Try like this
LOAD *, Sale+ Alt(Peek('Roll'), 0) as Roll INLINE [
Month, Sale, Rolling 12
Month1, 3,
Month2, 4,
Month3, 7,
Month4, 4,
Month5, 4, 22
Month6, 2, 24
Month7, 1, 25
Month8, 6, 31
Month9, 9, 40
Month10, 10, 50
Month11, 4, 54
Month12, 3, 57
Month13, 2, 59
Month14, 6, 65
Month15, 7, 72
Month16, 2, 74
];
Use Month as Dimension
Roll as Expression
Result in table box:
Month | Roll | Rolling 12 | Sale |
---|---|---|---|
Month1 | 3 | 3 | |
Month2 | 7 | 4 | |
Month3 | 14 | 7 | |
Month4 | 18 | 4 | |
Month5 | 22 | 22 | 4 |
Month6 | 24 | 24 | 2 |
Month7 | 25 | 25 | 1 |
Month8 | 31 | 31 | 6 |
Month9 | 40 | 40 | 9 |
Month10 | 50 | 50 | 10 |
Month11 | 54 | 54 | 4 |
Month12 | 57 | 57 | 3 |
Month13 | 59 | 59 | 2 |
Month14 | 65 | 65 | 6 |
Month15 | 72 | 72 | 7 |
Month16 | 74 | 74 | 2 |
Thanks!
But, can we have any other option for not to do any changes in back end script and directly writing expression for Roll in Graph front end.
Hi
Refer this
Calculating rolling n-period totals, averages or other aggregations
May be if you want to do this in Front End try something like this
Expression : rangesum(above(sum(Sale),1,RowNo())) * sum({<Month={"=Monthnum>max(total Monthnum)-12"}>}1)
Hi Thanks!
This expression showing correct value but, it only shows only last month bar not all the bars for 12 months,
Would you be able to share a sample where this isn't working?
Hi ,
Have you created the Monthnum column and tried ? Your script should have something like that
LOAD *,num(PurgeChar(Month,'Month'),'00') as Monthnum INLINE [
Month, Sale, Rolling 12
Month1, 3,
Month2, 4,
Month3, 7,
Month4, 4,
Month5, 4, 22
Month6, 2, 24
Month7, 1, 25
Month8, 6, 31
Month9, 9, 40
Month10, 10, 50
Month11, 4, 54
Month12, 3, 57
Month13, 2, 59
Month14, 6, 65
Month15, 7, 72
Month16, 2, 74
];
And then try the expression and let me know . Please check the attachment as well