topic Re: Rangesum or aggr or ?? in QlikView

Rangesum or aggr or ??

Michiel_QV_Fan — Mon, 24 Dec 2012 12:47:34 GMT

Hi,

I have the following challenge:

I need to have a table where

dimension = first day of the month

expression = count of files

We have a starting value at the beginning of the year and we need to calculate like this:

The first value = 100 and has been identified bij status code: 'running'. The new values are identified by status code: 'new'.

Date	Starting	new	Ending
2012-01-01	100	10	110
2012-02-01	110	-5	105
2012-03-01	105	60	165
2012-04-01	165	-20	145
2012-05-01	145	1	146
etc.

We also need to have the Starting value calculated correctly when for instance 2012-03-01 is selected:

Date	Starting	New	Ending
2012-03-01	105	60	165
2012-04-01	165	-20	145
etc.

What function do I need to achieve this?

Thanks and happy holidays!

Re: Rangesum or aggr or ??

Gysbert_Wassenaar — Mon, 24 Dec 2012 13:11:41 GMT

Calculate the values in the script using the peek function:

load

Date,

if([status code]='running', count_of_files, peek('Starting' + peek('count_of_files') as Starting,

if([status code]='new', count_of_files, 0) as new,

if([status code]='running', count_of_files, Starting + count_of_files as Ending

from ....

Re: Rangesum or aggr or ??

Michiel_QV_Fan — Mon, 24 Dec 2012 13:29:18 GMT

Additional info is needed .

The starting value = calculated from the field where statuscode = running.

This results in 1 value because running is only once in the data, at 2012-01-01. Therefore each Starting value is the sum of running at 2012-01-01 + new.

Thanks