
Re: Conditional formatting and comparison with the median value in the same pivot table
Stefan Wühl May 24, 2016 6:45 AM (in response to Filiberto Cacciari)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: Conditional formatting and comparison with the median value in the same pivot table
Sunny Talwar May 24, 2016 6:48 AM (in response to Stefan Wühl )You are behind my samples


Re: Conditional formatting and comparison with the median value in the same pivot table
Sunny Talwar May 24, 2016 6:48 AM (in response to Filiberto Cacciari)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%'))

Test SlowMoving Analysis.qvw 155.5 K

Re: Conditional formatting and comparison with the median value in the same pivot table
kushal chawda May 24, 2016 7:00 AM (in response to Sunny Talwar )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 May 24, 2016 7:03 AM (in response to kushal chawda )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 May 24, 2016 7:12 AM (in response to Sunny Talwar )Just implement, don't do any planning



Re: Conditional formatting and comparison with the median value in the same pivot table
Filiberto Cacciari May 24, 2016 7:35 AM (in response to Sunny Talwar )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?

Re: Conditional formatting and comparison with the median value in the same pivot table
Sunny Talwar May 24, 2016 7:37 AM (in response to Filiberto Cacciari)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
Stefan Wühl May 24, 2016 7:48 AM (in response to Filiberto Cacciari)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).

Test SlowMoving Analysis_2.qvw 154.5 K


Re: Conditional formatting and comparison with the median value in the same pivot table
Sunny Talwar May 25, 2016 4:49 AM (in response to Filiberto Cacciari)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
Stefan Wühl May 25, 2016 4:50 AM (in response to Filiberto Cacciari)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
Sunny Talwar May 25, 2016 4:55 AM (in response to Filiberto Cacciari)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())

Test SlowMoving Analysis_3.qvw 156.0 K


Re: Conditional formatting and comparison with the median value in the same pivot table
Stefan Wühl May 25, 2016 4:57 AM (in response to Filiberto Cacciari)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




