Cumulative sum in bar chart

[vijetas42]

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

[MayilVahanan]

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

[vijetas42]

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.

[MayilVahanan]

Hi

Refer this

Calculating rolling n-period totals, averages or other aggregations

[qliksus]

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)

[vijetas42]

Hi Thanks!

This expression showing correct value but, it only shows only last month bar not all the bars for 12 months,

[sunny_talwar]

Would you be able to share a sample where this isn't working?

[qliksus]

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