topic Re: Conditional Sum in QlikView

Conditional Sum

Anonymous — Wed, 11 Dec 2013 16:01:36 GMT

I am trying to create Cost Centre Budget Report. The data im using is from a database in raw form so having to manipulate it to create the CC report.

Period Budget YTD Spend YTD Spend

1 1000 1000 800 800

2 1000 2000 800 1600

3 1000 3000 800 2400

4 1000 4000 800 3200

5 1000 5000 800 4000

6 1000 6000 800 4800

And So on....

I have the budget & Spend figures but need to create YTD Budget & YTD Spend totals.

Can anyone help.

Regards

Re: Conditional Sum

Anonymous — Wed, 11 Dec 2013 16:18:25 GMT

Is it worth considering the 'peek' function, giving you something like:

Load Period,

Budget,

Budget + Peek('YTD',-1) as YTD,

Spend,

Spend + Peek('[YTD Spend]',-1) as [YTD Spend]

I'm not sure how you would initiate the YTD and YTD Spend fields, probably need to do an if statement to identify the first row of each new year and set the value to Budget or Budget Spend. This is not tested so is only really for a rough idea rather than a certain solution.

Regards,

Adam

Re: Conditional Sum

Wed, 11 Dec 2013 16:18:29 GMT

Hi Naheem,

You may try the above() function in your chart or you can use accumulate.

Find here some info regarding accumulate:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/12/accumulative-sums

Regards,

Gerrit