Conditional formatting and comparison with the median value in the same pivot table

[caccio88]

Hi everyone,

what I would like to do is to display in the same table the example you see in my excel attached in sheet "TO Display", you can see also the formulas.

I tried to do that in qvw but as you can see in my attached qvw, I'm stopped at the calculation of the % per Buyer/MonthYear.

Do you think that is possible to implement my excel example exactly in the same pivot table qvw?

Maybe I have to separate some of those information, average or median, in some other table?

Thanks for your help.

Filiberto

stalwar1‌ gwassenaar‌ swuehl‌

[swuehl]

You can probably do this using a synthetic dimension, similar to what is shown here

Re: Show Pivot and Straight Table in One Table

I guess Sunny is already preparing a sample.

edit: this sample might be closer to your requirements:

Re: Add count to pivot table

[sunny_talwar]

This?

Script:

LOAD MonthYear,

     Stock,

     Devaluation,

     Buyer

FROM

[Data.xlsx]

(ooxml, embedded labels);

Dim:

LOAD * Inline [

Dim

1

2

3

4];

Dimension:

Buyer

=Pick(Dim, MonthName(MonthYear), 'Average', 'Stock_Apr16', 'Median')

Expression

=Pick(Dim, Num(Sum(Devaluation)/Sum(Stock), '#.##0,0%'), Num(Sum(Devaluation)/Sum(Stock), '#.##0,0%'), Num(Sum({<MonthYear = {"$(=Max(MonthYear))"}>}Stock), '#.##0,00'), Num(Median(TOTAL Aggr(Sum(Devaluation)/Sum(Stock), Buyer, MonthYear)), '#.##0,0%'))

[sunny_talwar]

You are behind my samples

[Kushal_Chawda]

stalwar1‌, you are still not writing blog on Pick

[sunny_talwar]

Hahahaha I am in the planning phase right now

[Kushal_Chawda]

Just implement, don't do any planning

[caccio88]

That seems exactly as mine. But I can't understand 2 things:

1) the average is a little bit different from the value calculated in excel. What could be the reason?

2) The median value of the 3 values (2,1%; 5,5% ; 10,2%) must be 5,5%. Using the value of your example (1,9%; 5,4%; 10,1%) must be 5,4%. Not 5,2%. Don't you are agree?

stalwar1‌

[sunny_talwar]

Filiberto‌

1) Could be the rounding issue, not entirely sure what's wrong there

2) QlikView has it's own way to calculate Median, I think its doing it own weird calculation (I will have test it to be sure, but see this Re: What is the exactly calculation of the fractile function?)

[swuehl · Accepted solution]

Maybe like this?

Buyer	=Pick(Dim, MonthName(MonthYear), 'Average', 'Stock_Apr16', 'Median')	feb 2016	mar 2016	apr 2016	Average	Stock_Apr16	Median
AB		6,1%	5,2%	5,1%	5,5%	20.048.775,67	5,5%
AG		2,3%	1,4%	2,5%	2,1%	1.529.309,27	5,5%
CT		11,5%	9,6%	9,5%	10,2%	3.903.171,50	5,5%

You are calculating the average of averages in your excel file, while Sunny is calculating the expression total average (which I would personally prefer).