Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Conditional Sum

Hi

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


2 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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