topic How to reference to other row's result in Pivot/Straight Table? in QlikView

How to reference to other row's result in Pivot/Straight Table?

Thu, 11 Feb 2016 13:14:19 GMT

Hi,

The great challenge that I face is the following:

I'd need to find the best publication type as per "Seasons", which is basically the one with the lowest CPC (cost per clik).

If we have that, than we'd need to compare that to the actual CPC and see the difference (when this is multiplied by impression figures, you'd get possible saved amount)

See example below

So, if we see Type 1 as an example - circled in red - you can see that I got 30 (20+10) CPCs with Pub1 publication, whereas Pub2 publication has 60 CPC.

That is an expensive solution, therefore in the "mistery calc" I would need to see, how the actual CPC varies from the minimum CPC as per Publications. Following the example you would see:

Row 1: 0 (as this is the cheapest solution)

Row 2: 10 (as it has a CPC that is higher with 10)

Row 3: 50 (highest value, biggest saving opp)

Blue example - following the same logic:

Row 4: 10

Row 5: 20

Row 6: 0 (most optimal solution for Type 2 Season, using Publication as group

Any suggestions on the expression?

Thanks in advance!

Tamas

Re: How to reference to other row's result in Pivot/Straight Table?

Gysbert_Wassenaar — Thu, 11 Feb 2016 16:32:06 GMT

Perhaps this: sum(CPC) - min(aggr(sum(CPC),Country,Segment,Season,Publication))

Re: How to reference to other row's result in Pivot/Straight Table?

Fri, 12 Feb 2016 08:34:47 GMT

Thanks for the quick reply Gysbert.

I was thinking the same, however, wouldn't it make sense to use min instead of sum within the aggr function?

min(aggr(min(CPC),Country,Segment,Season,Publication)) ?

Anyway, the aggr returns back the optimal value, BUT only at the actual row where it sits. At other records it shows NULL. See screenshot below.

How can I make the value 10 appear in each and every related rows (in this case the first two)?

Re: How to reference to other row's result in Pivot/Straight Table?

Gysbert_Wassenaar — Fri, 12 Feb 2016 17:29:34 GMT

Maybe, but I can't know if your CPC expression is already a sum aggregation or not.

Try this one: sum(CPC) - min(aggr(nodistinct sum(CPC),Country,Segment,Season,Publication))

Re: How to reference to other row's result in Pivot/Straight Table?

Mon, 15 Feb 2016 08:16:18 GMT

Gysbert, I cannot express my gratitude!

CPC indeed has a sum aggregation so I had to use min, but the framework itself works!

Thanks a bunch!

Tamas