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‌

[caccio88]

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?

[sunny_talwar]

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.

[swuehl]

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

[caccio88]

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‌

[sunny_talwar]

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

[swuehl]

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