Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Highlighted
Not applicable

Pivot table - Highlight data based on previous rows (multiple dimension)

Hello all,

I'm trying to format a pivot table based on previous/above rows.

Let says I have this table :

ScenarioProductMeasure
ReferenceA12
ReferenceB20
ReferenceC15
Scenario 1A10
Scenario 1B21
Scenario 1C15

I would like to compare the measure value of products in both scenario.

In the above example, only product A and B should be highlighted because their value changed between both scenario.

I tryed something like this but it doesn't work :

if(Scenario<> 'Reference',

  if(SUM( {1 <Scenario={'Reference'}>} Measure) <> SUM( {1 <Scenario={'Scenario 1'}>} Measure), green(100))

)

Any idea ?

Regards

1 Solution

Accepted Solutions
Not applicable

Re: Pivot table - Highlight data based on previous rows (multiple dimension)

Hi Marcus

Use the Total <field> within the function to total by the product, then you can change the formula to do a number of things that suit your individual requirements eg count distinct scenario, or sum all the measures.

EG Try

 

 

=(

sum(total <Product> Measure)/ sum(Measure) <>2)
*(
Scenario = 'Scenario 1')*green(100)

as the Highlighting condition.

This checks that the total measure is equal between the two scenarios, and that the scenario the line is on is "scenario1".

Let me know if this works

Erica

5 Replies

Re: Pivot table - Highlight data based on previous rows (multiple dimension)

Not very elegant, but it worked. I think there are ways to simplify this. Also it could be an alternative to change the datamodel into an (additionally) table like this:

table:

Product     MeasureReference     MeasureScenario1

- Marcus

Not applicable

Re: Pivot table - Highlight data based on previous rows (multiple dimension)

Hi Marcus

Use the Total <field> within the function to total by the product, then you can change the formula to do a number of things that suit your individual requirements eg count distinct scenario, or sum all the measures.

EG Try

 

 

=(

sum(total <Product> Measure)/ sum(Measure) <>2)
*(
Scenario = 'Scenario 1')*green(100)

as the Highlighting condition.

This checks that the total measure is equal between the two scenarios, and that the scenario the line is on is "scenario1".

Let me know if this works

Erica

Not applicable

Re: Pivot table - Highlight data based on previous rows (multiple dimension)

Thanks Marcus for your answer.

Indeed, it's not perfect, specialy because everytime a new product will be added, the formula will have to be updated.

Also, I tryed to play with above() and rowNo() function, but I think above() doesn't accept dynamic offset.

If I can't find a better solution, I will follow your advice and change the datamodel.

- Jean

Not applicable

Re: Pivot table - Highlight data based on previous rows (multiple dimension)

Hi Erica,

Indeed, your solution is clever and works very well !

Thank you very much !

Not applicable

Re: Pivot table - Highlight data based on previous rows (multiple dimension)

No problem!

Community Browser