topic Re: Mode and Prices in QlikView

Mode and Prices

Tue, 26 Nov 2013 06:29:11 GMT

Hello!

I have a little table like this:

Period	City	Sku	Brand	Price	Sales
201301	A	Sku1	Alpha	1.5	100
201301	A	Sku2	Alpha	1.5	150
201301	A	Sku3	Beta	1.6	120
201301	B	Sku1	Alpha	1.4	130
201301	B	Sku2	Alpha	1.4	120
201301	B	Sku3	Beta	1.3	100
201301	C	Sku1	Alpha	1.5	120
201301	C	Sku2	Alpha	1.4	110
201301	C	Sku3	Beta	1.3	130
201301	C	Sku4	Gamma	1.3	130
201301	C	Sku5	Gamma	1.3	150

I just want to build a Pivot Table that calculates the sum of the sales by city where the price equals the modal price (also by city).

I can't seem to find out how to solve this problem.

Any help would be really appreciated!

PS: I'm attaching a qvw with the table and what I've done so far...

Re: Mode and Prices

hic — Tue, 26 Nov 2013 11:50:38 GMT

Sum(If(Price=Aggr(Mode(Price),Period,City), Sales))

HIC

Re: Mode and Prices

sujeetsingh — Tue, 26 Nov 2013 12:12:34 GMT

Please explain more deep

Re: Mode and Prices

matthias_v — Tue, 26 Nov 2013 12:22:02 GMT

Hi Ruben,

I adjusted your example to fit your needs (see attachment). I placed the logic in the script as to gain performance towards your end-users.

Re: Mode and Prices

Tue, 26 Nov 2013 12:26:12 GMT

Thanks for your answer Henric Cronström !

I used your expression but, sadly, I'm still not getting the correct numbers:

The correct numbers should be the ones on the textboxes to the right.

Am I doing something wrong?

Re: Mode and Prices

hic — Tue, 26 Nov 2013 12:41:21 GMT

Set analysis is not the answer here, since Ruben wants to make a comparison on every row of the pivot table. Set analysis will use a common "selection" for all rows.

So, instead I use the Aggr() function, which in this case calculates the most common value (Mode(Price)) for each combination of Period and City, and then this value is used to select the relevant transactions.

HIC

Re: Re: Mode and Prices

Tue, 26 Nov 2013 12:43:36 GMT

Hi Matthias,

Thanks for your answer!

I tried you approach and it works, I get the numbers.

But can this be done in a more "dynamic" way?

I just used "period" and "city" as dimensions, but if I add another (let's say "brand") I would have to reconfigure the LEFT JOIN in your script

Thanks in advance!

Re: Mode and Prices

hic — Tue, 26 Nov 2013 12:58:58 GMT

Sorry, I forgot a "nodistinct"

Sum(If(Price=Aggr(nodistinct Mode(Price),Period,City), Sales))

HIC

Re: Mode and Prices

matthias_v — Tue, 26 Nov 2013 13:03:10 GMT

Hey Ruben,

In that case Henric has the answer you seek.

Re: Mode and Prices

Tue, 26 Nov 2013 13:17:41 GMT

Excellent Henric!!!!

It works!!!

Re: Mode and Prices

Tue, 26 Nov 2013 13:21:14 GMT

That's right, I just used the modified expression he posted and it works like a charm

Nevertheless, your answer is also correct. It depends on the scenario I guess.

Thanks again!