Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

naheemali
New Contributor

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
abh
Contributor II

Re: Conditional Sum

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

Re: Conditional Sum

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

Community Browser