Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Qlikview Highlight Min and Max value in Pivot table

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.

   

CoNameCompany1Company2Company3
Sep-16Sep-15Var +/-Sep-16Sep-15Var +/-Sep-16Sep-15Var +/-
Reportitems12.42.5(0.1)2.62.60.02.82.9(0.1)
Reportitems115.75.9(0.1)5.95.90.05.25.2(0.0)
Reportitems83.53.6(0.1)3.73.60.12.12.2(0.1)
Reportitems225.525.7(0.1)31.129.12.038.131.46.7
Reportitems346.641.65.038.539.6(1.1)48.446.91.5
Reportitems514.415.5(1.1)14.515.0(0.5)13.717.3(3.6)
Reportitems61.41.4(0.0)1.71.60.11.81.9(0.1)
Reportitems72.32.5(0.2)2.62.8(0.2)2.42.6(0.2)

   

 

 

 

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: Qlikview Highlight Min and Max value in Pivot table

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


Capture.PNG

View solution in original post

9 Replies
Highlighted
Partner
Partner

Re: Qlikview Highlight Min and Max value in Pivot table

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

Highlighted
Not applicable

Re: Qlikview Highlight Min and Max value in Pivot table

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)

Highlighted

Re: Qlikview Highlight Min and Max value in Pivot table

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Not applicable

Re: Qlikview Highlight Min and Max value in Pivot table

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)

   

CoNameCompany1Company2Company3Company4Company5
Sep-16Sep-15Var +/-Sep-16Sep-15Var +/-Sep-16Sep-15Var +/-Sep-16Sep-15Var +/-Sep-16Sep-15Var +/-
Reportitems346.641.65.038.539.6(1.1)48.446.91.547.853.2(5.5)55.754.51.2

 

Highlighted

Re: Qlikview Highlight Min and Max value in Pivot table

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

Capture.PNG

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Not applicable

Re: Qlikview Highlight Min and Max value in Pivot table

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.

Highlighted

Re: Qlikview Highlight Min and Max value in Pivot table

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted

Re: Qlikview Highlight Min and Max value in Pivot table

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


Capture.PNG

View solution in original post

Highlighted
Not applicable

Re: Qlikview Highlight Min and Max value in Pivot table

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