topic Re: Cumulated measure in QlikView

Cumulated measure

Anonymous — Tue, 31 May 2016 08:09:05 GMT

Hi guys,

I've a necessity to build a chart with an cumulative calculation.

Dimensions: Container id, Date

Measures: PRI = if day(Date) = 1 then 1 else TWRR * previous(PRI) end

TWRR is another calculation and can be disregarded.

How to implement this calculation?

Thanks.

Re: Cumulated measure

PrashantSangle — Tue, 31 May 2016 08:12:13 GMT

Hi,

use above or below as per requirement

try like

expression,

if(day(Date)=1,1,TWRR*above(PRI))

Regards

Re: Cumulated measure

Anonymous — Tue, 31 May 2016 08:28:10 GMT

PRI = above(PRI) renders null. I can't reference myself (chart column) in the Above function. I can only reference some other field, right?

Re: Cumulated measure

PrashantSangle — Tue, 31 May 2016 08:49:28 GMT

Hi,

PRI is column Name or expression???

Syntax :

Above(expr)

qlik below url for detail understanding of above()

Above ‒ QlikView

Regards

Re: Cumulated measure

Anonymous — Tue, 31 May 2016 08:55:52 GMT

As per the definition, PRI is the name of the calculation. Which is defined in this way:

PRI = if (day(Date) = 1, 1, CONST * prev(PRI))

So, PRI is eventually a chart column which is designed as a static type of a counter which does not depend on any other field in the data model.

Therefore, PRI = above(PRI) seems impossible for me. And I don't see any other way of implementing this formula in Qlikview except for moving it in the script.

Or am I wrong?

Re: Cumulated measure

PrashantSangle — Tue, 31 May 2016 09:00:06 GMT

Hi,

I am not getting you..

can you provide sample data with required output???

Regards,

Re: Cumulated measure

Anonymous — Tue, 31 May 2016 09:34:10 GMT

Let's start with this one:

for each date I need to make this calculation.

Re: Cumulated measure

sunny_talwar — Tue, 31 May 2016 10:01:23 GMT

Is it possible to provide the raw data for this in a Excel file to test it out?

Re: Cumulated measure

Anonymous — Tue, 31 May 2016 10:04:25 GMT

Sure.

Find below:

Date	PRI
01/01/2016	15
02/01/2016	22.5
03/01/2016	33.75
04/01/2016	50.625
05/01/2016	75.9375
06/01/2016	113.90625
07/01/2016	170.859375
08/01/2016	256.289063
09/01/2016	384.433594
10/01/2016	576.650391
11/01/2016	864.975586
12/01/2016	1297.46338
13/01/2016	1946.19507
14/01/2016	2919.2926
15/01/2016	4378.9389
16/01/2016	6568.40836
17/01/2016	9852.61253
18/01/2016	14778.9188
19/01/2016	22168.3782
20/01/2016	33252.5673
21/01/2016	49878.851
22/01/2016	74818.2764
23/01/2016	112227.415
24/01/2016	168341.122
25/01/2016	252511.683
26/01/2016	378767.524
27/01/2016	568151.287
28/01/2016	852226.93
29/01/2016	1278340.39
30/01/2016	1917510.59
31/01/2016	2876265.89
01/02/2016	15
02/02/2016	22.5
03/02/2016	33.75
04/02/2016	50.625
05/02/2016	75.9375
06/02/2016	113.90625
07/02/2016	170.859375
08/02/2016	256.289063
09/02/2016	384.433594
10/02/2016	576.650391
11/02/2016	864.975586
12/02/2016	1297.46338
13/02/2016	1946.19507
14/02/2016	2919.2926
15/02/2016	4378.9389
16/02/2016	6568.40836
17/02/2016	9852.61253
18/02/2016	14778.9188
19/02/2016	22168.3782
20/02/2016	33252.5673
21/02/2016	49878.851
22/02/2016	74818.2764
23/02/2016	112227.415
24/02/2016	168341.122
25/02/2016	252511.683
26/02/2016	378767.524
27/02/2016	568151.287
28/02/2016	852226.93
29/02/2016	1278340.39

Re: Cumulated measure

sunny_talwar — Tue, 31 May 2016 10:06:35 GMT

Isn't this the output you are looking to get? What is CONST here?

Re: Cumulated measure

Anonymous — Tue, 31 May 2016 10:09:22 GMT

The initial PRI value is 15 and this is constant.

The CONST is 1.5 in the example.

But in the reality it changes for each container and each date. Though, it does not matter, I put it here just to show that we have some calculation and not just a simple reference to the previous value.

Re: Cumulated measure

sunny_talwar — Tue, 31 May 2016 10:17:38 GMT

Seems to be working:

Re: Cumulated measure

PrashantSangle — Tue, 31 May 2016 10:24:31 GMT

good one bro

Re: Cumulated measure

Anonymous — Tue, 31 May 2016 10:24:48 GMT

Yes, indeed, it seems to be working. I wonder how come it is not working in my case... I'll keep investigating...

Re: Cumulated measure

sunny_talwar — Tue, 31 May 2016 10:30:48 GMT

Are you making selections in Date field? This will tend to break with selections in date. By break I mean that the values will change and won't remain the same

Re: Cumulated measure

Anonymous — Tue, 31 May 2016 10:36:47 GMT

I know this. And as for the time being this is fine.

Though, I wonder now if I can use this formula or a similar one in the Aggr function.

See, my actual problem is that I have a table with Container, Product and Date. And one calculation on the Product level requires this PRI calculation on the Container level.

So, the formula would look like this: TWRR (which is on container, product, date) * PRI (which is on container, date).

So, this would eventually be something like:

=TWRR * sum( aggr( [PRI CALCULATION HERE], Container, Date ))

That is why I was initially trying to replicate this calculation in the straight table to check its general feasibility.

Would that be possible?

Re: Cumulated measure

sunny_talwar — Tue, 31 May 2016 10:59:12 GMT

You concern is valud and I don't think this will work within Aggr(). Don't knowing your data very well, but still have a feeling that we might be able to avoid Aggr. To test this out, we would need some sample data as close to your actual data. It would also be helpful to have the expected output so that we can aim to match it up with the actual data.

Re: Cumulated measure

Anonymous — Tue, 31 May 2016 11:06:49 GMT

In the meanwhile, I found the reason why the Above did not work in my case: I had 3 dimensions in the straight table. Above works with 2 at max. So weird... 😕

Re: Cumulated measure

Anonymous — Tue, 31 May 2016 11:11:13 GMT

And

Load 1 as T, *;

Load * INLINE [....];

for appending new fields is so helpful too. Thanks for it.

Re: Cumulated measure

sunny_talwar — Tue, 31 May 2016 11:18:01 GMT

You can try this:

If(Day(Date) = 1, PRI, 1.5 * Above(TOTAL PRI_Label))