topic Re: Conditional formatting and comparison with the median value in the same pivot table in QlikView

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

caccio88 — Tue, 24 May 2016 10:34:19 GMT

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‌

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

swuehl — Tue, 24 May 2016 10:41:11 GMT

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

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

sunny_talwar — Tue, 24 May 2016 10:48:07 GMT

This?

Script:

LOAD MonthYear,

Stock,

Devaluation,

Buyer

FROM

[Data.xlsx]

(ooxml, embedded labels);

Dim:

LOAD * Inline [

Dim

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

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

sunny_talwar — Tue, 24 May 2016 10:48:54 GMT

You are behind my samples

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

Kushal_Chawda — Tue, 24 May 2016 11:00:29 GMT

stalwar1‌, you are still not writing blog on Pick

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

sunny_talwar — Tue, 24 May 2016 11:03:06 GMT

Hahahaha I am in the planning phase right now

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

Kushal_Chawda — Tue, 24 May 2016 11:12:58 GMT

Just implement, don't do any planning

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

caccio88 — Tue, 24 May 2016 11:35:10 GMT

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‌

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

sunny_talwar — Tue, 24 May 2016 11:37:28 GMT

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

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

swuehl — Tue, 24 May 2016 11:48:54 GMT

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

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

caccio88 — Wed, 25 May 2016 08:47:21 GMT

You're right swuehl‌! I'll think about which solution, that you and stalwar1‌ have proposed, could be the best for me.

Anyway I think that median value in the solution of stalwar1‌ should be 5,4% and not 5,2%. Don't you think so?

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

sunny_talwar — Wed, 25 May 2016 08:49:27 GMT

I think my solution may be suffering from the same issue. It might still be picking the Median from the original data rather than the median from the three averages. I am not 100% sure though. Lets see what Stefan mentions.

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

swuehl — Wed, 25 May 2016 08:50:15 GMT

Anyway I think that median value in the solution of stalwar1 should be 5,4% and not 5,2%. Don't you think so?

I think that's correct (if you refer to the median of the average column).

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

caccio88 — Wed, 25 May 2016 08:51:15 GMT

Another thing....

If I would like to format in red color the values of Average that is greater than the median value, how could I do it?

Like what I've done in Excel...

swuehl‌ stalwar1‌

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

sunny_talwar — Wed, 25 May 2016 08:55:06 GMT

May be like this in the background color for the expression:

=If(Dim = 2 and Avg(Aggr(NODISTINCT Sum(Devaluation)/Sum(Stock),Buyer,MonthYear)) = Max(TOTAL Aggr(NODISTINCT Avg(Aggr(NODISTINCT Sum(Devaluation)/Sum(Stock),Buyer,MonthYear)), Buyer)), Red())

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

swuehl — Wed, 25 May 2016 08:55:26 GMT

Use a background color attribute expression like

=If(Dim = 2 and

Avg(Aggr(NODISTINCT Sum(Devaluation)/Sum(Stock),Buyer,MonthYear)) >

Median(TOTAL Aggr( Avg(Aggr(Sum(Devaluation)/Sum(Stock), Buyer, MonthYear)),Buyer))

, lightred()

)

edit: removed the unneeded number formatting