Qlikview Highlight Min and Max value in Pivot table

[#1]

Hi

Referring to table below, I would like to highlight the background color of variance "Var +/-" for Max value to blue and Min value to yellow for Reportitems3 but was unable to do so. Appreciate it if someone could help. Attached is the sample qvw and excel file for table below.

CoName	Company1			Company2			Company3
	Sep-16	Sep-15	Var +/-	Sep-16	Sep-15	Var +/-	Sep-16	Sep-15	Var +/-
Reportitems1	2.4	2.5	(0.1)	2.6	2.6	0.0	2.8	2.9	(0.1)
Reportitems11	5.7	5.9	(0.1)	5.9	5.9	0.0	5.2	5.2	(0.0)
Reportitems8	3.5	3.6	(0.1)	3.7	3.6	0.1	2.1	2.2	(0.1)
Reportitems2	25.5	25.7	(0.1)	31.1	29.1	2.0	38.1	31.4	6.7
Reportitems3	46.6	41.6	5.0	38.5	39.6	(1.1)	48.4	46.9	1.5
Reportitems5	14.4	15.5	(1.1)	14.5	15.0	(0.5)	13.7	17.3	(3.6)
Reportitems6	1.4	1.4	(0.0)	1.7	1.6	0.1	1.8	1.9	(0.1)
Reportitems7	2.3	2.5	(0.2)	2.6	2.8	(0.2)	2.4	2.6	(0.2)

[RonaldDoes]

Hi,

To determine the maximum and minimum variation, I've used AGGR.

Max(TOTAL AGGR((Sum({<Period = {'$(vCurrentQY)'}>} Amount)-Sum({<Period = {'$(vSelectQY)'}>} Amount)), CoName, Reportitems))

Once you have this, you can add it to a conditional background color expression as:

=If(
(
Sum({<Period = {'$(vCurrentQY)'}>} Amount)
-
Sum({<Period = {'$(vSelectQY)'}>} Amount)
)
=
Max(TOTAL AGGR((Sum({<Period = {'$(vCurrentQY)'}>} Amount)-Sum({<Period = {'$(vSelectQY)'}>} Amount)), CoName, Reportitems))
,
RGB(83,141,213)
,
If(
(
Sum({<Period = {'$(vCurrentQY)'}>} Amount)
-
Sum({<Period = {'$(vSelectQY)'}>} Amount)
)
=
Min(TOTAL AGGR((Sum({<Period = {'$(vCurrentQY)'}>} Amount)-Sum({<Period = {'$(vSelectQY)'}>} Amount)), CoName, Reportitems))
,
Yellow()
,
NULL()
)
)

I've attached an example, please let me know if this works for you.

With kind regards,

Ronald

[#3]

Hi Ronald,

Thank you for your prompt reply. Actually what I wanted is the get the Min and Max variance within the same Row. (ie, Reporttems3 - To highlight background color for company having min or max variance in Reportitems3)

[Anil_Babu_Samineni]

If else condition works only dimension level not in Metric level. And you are suppose to use specified value for it. It can nod be done. And you are highlighted only for specific values. How you figure out to fix the high light. Only two values you high lighted which called 5.0 and 1.1 Can you elaborate more

[#5]

Hi Anil,

Sorry! In my earlier post, I show only 3 companies. In my qvw, I have 5 companies.

Referring to the table below, what I wanted is the Max and Min for Reportitems3.

In this case, Company1 has the Max variance of 5.0 (highlight Blue) while company4 has min variance of (5.5) (Highlight Yellow)

CoName	Company1			Company2			Company3			Company4			Company5
	Sep-16	Sep-15	Var +/-	Sep-16	Sep-15	Var +/-	Sep-16	Sep-15	Var +/-	Sep-16	Sep-15	Var +/-	Sep-16	Sep-15	Var +/-
Reportitems3	46.6	41.6	5.0	38.5	39.6	(1.1)	48.4	46.9	1.5	47.8	53.2	(5.5)	55.7	54.5	1.2

[Anil_Babu_Samineni]

Like this?

=If( Column(3)= Max(TOTAL AGGR((Sum({<Period = {'$(vCurrentQY)'}>} Amount)-Sum({<Period = {'$(vSelectQY)'}>} Amount)), CoName, Reportitems)) , Blue() , 

If(  Column(3)=  Min(TOTAL AGGR((Sum({<Period = {'$(vCurrentQY)'}>} Amount)-Sum({<Period = {'$(vSelectQY)'}>} Amount)), CoName, Reportitems)) , Yellow()  ,White() ))

And, I've changed you format to #,##0.00

[#7]

Hi Anil,

This produce the same result as  Ronald solution. It gives the Min and Max based on all the reportitems in the table. What I want is to get the min and max by individual reportitems row. For this instance, min and max for Reportitem3.

[Anil_Babu_Samineni]

Yes, That's how I've mentioned Row level won't possible by using direct help

May be look How to use - Dimensionality()

May be this helps

[sunny_talwar · Accepted solution]

May be like this

=If(Reportitems = 'Reportitems3',

  If(Column(3) = Max(TOTAL <Reportitems> Aggr(Sum({<Period = {'$(vCurrentQY)'}, Report = {'R2'}>} Amount)-Sum({<Period = {'$(vSelectQY)'}, Report = {'R2'}>} Amount), Reportitems, CoName)), Blue(),

  If(Column(3) = Min(TOTAL <Reportitems> Aggr(Sum({<Period = {'$(vCurrentQY)'}, Report = {'R2'}>} Amount)-Sum({<Period = {'$(vSelectQY)'}, Report = {'R2'}>} Amount), Reportitems, CoName)), Yellow())))

[#10]

Hi Sunny,

Great!! You are a genius. This is exactly what I wanted. Thank You.

Ronald and Anil,

Thank you for your time in looking into this.

Rgds,

Low