topic Re: Cumulative sum with two dimension. in QlikView

Cumulative sum with two dimension.

Fri, 11 Oct 2013 14:00:58 GMT

Hi everybody, Could you Please help me in finding solution for this problem. I am attaching the data sheet with the problem.

I have a date field and an Amount field.

I have to show the data in a straight table as cumulative sum of Amount.

the Conditions are:

Here the cumulative sum of 3rd row will be sum of 3rd 2nd and 1st Amount.
The year starts at April and ends at March so when April 2013 comes, the sum should start again.
The value 0 should be displayed in the chart.

Can it be done. If I remove the Amount field. I can easily get the cumulative sum. But adding Amount dimension creates a problem.

mrybalko — Fri, 11 Oct 2013 14:34:38 GMT

I suggest to calculate cumulative sum by loading script.

Two tips:

Calculate finance year

year(AddMonths(Date, -3)) as FinanceYear

Then use functions previous and peek to calculate cumulative sum

if(FinanceYear=Previous(FinanceYear),

peek('CumulativeAmount') + Amount,

Amount) as CumulativeAmount

Example in attachment.

Fri, 11 Oct 2013 15:09:47 GMT

Thanks Maxim

Thanks a lot.

Sun, 13 Oct 2013 08:52:19 GMT

I got the solution for it. If we take year as a seperate field in load statement like shown below.

LOAD Date,

Year(Date),

if(Month(Date)='Jan',Year(Date)-1,if(Month(Date)='Feb',Year(Date)-1,if(Month(Date)='Mar',Year(Date)-1,Year(Date)))) as FY,

Amount

FROM

CUMULATIVE.xlsx.

Now in a chart i.e straight table take Date as dimension, FY as dimension, Amount as Expression and Sum(Amount) as Expression.

Make sure the Accmulation is checked and Hide Fy . You can get the desired result set.

Thu, 22 Jan 2015 15:40:55 GMT

Just to clean that up a bit:

LOAD

Date,

Year(Date)

if(Num(Month(Date))<=3,Year(Date)-1,Year(Date) As FY,

etc.