Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

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

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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

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))


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

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


talk is cheap, supply exceeds demand
Highlighted
Not applicable

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

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)?

Highlighted
MVP & Luminary
MVP & Luminary

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

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))


talk is cheap, supply exceeds demand

View solution in original post

Highlighted
Not applicable

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

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