topic Re: Cumulative sum in bar chart in QlikView

Cumulative sum in bar chart

vijetas42 — Thu, 11 Aug 2016 05:21:25 GMT

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

Re: Cumulative sum in bar chart

MayilVahanan — Thu, 11 Aug 2016 05:26:30 GMT

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

Re: Cumulative sum in bar chart

vijetas42 — Thu, 11 Aug 2016 05:49:11 GMT

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.

Re: Cumulative sum in bar chart

MayilVahanan — Thu, 11 Aug 2016 06:00:29 GMT

Refer this

Calculating rolling n-period totals, averages or other aggregations

Re: Cumulative sum in bar chart

qliksus — Thu, 11 Aug 2016 06:02:36 GMT

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)

Re: Cumulative sum in bar chart

vijetas42 — Thu, 11 Aug 2016 06:19:05 GMT

Hi Thanks!

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

Re: Cumulative sum in bar chart

sunny_talwar — Thu, 11 Aug 2016 11:12:31 GMT

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

Re: Cumulative sum in bar chart

qliksus — Thu, 11 Aug 2016 12:15:53 GMT

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