Exclude 0-values in pivot-table conditional colour, when comparing values.

[#1]

.. Yeah i really didn't know what to call this thread..

Business Case: Compare values from user to a benchmark. Colour values red() if value is lower than benchmark - pretty simple.

My problem is this:

i have a pivot table with values for user A. These values are to be compared to a Benchmark group (known as User X). However, when User A only have values from 2010-2015, but user X has values from 2005-2015 i get a lot of meaningless columns with zero-values in 2005-2009 due to the conditional colour expression below:

----

if(

sum(Amount)

<

sum(

{<

User = {'User X'}

>}

Amount), red())

----

If i remove the colour expression i obviously only get data for the years i want, and as such (and since it is not a measure by itself) i really didn't think that by adding a colour expression, i would add data to the pivot table... I just need to get rid of it again..

I tried multiple variations of if-statements and set expressions, but can't seem to get it done - Help please

Thanks.

[ogster1974]

Have you tried included Year in your pivot table?  I think this is why you are getting duplicates.

[#3]

Hi Andy,

Year is being shown as the only thing horizontally (on columns).. The problem is that the years 2005, 2006, 2007, 2008 and 2009 magically appears whenever i make the above mentioned colour expression because "User X" has data on these years.

[ogster1974]

Perhaps a change to your data model will solve this.

I take it User X only function is to benchmark your other users on a yearly basis?  If yes

Create a new table called UserBenchmark

With Year and BenchmarkAmount values

Qlik will automatically join on the year between your user facts and your benchmark data if named the same then do

if(

sum(Amount)

<

sum(

BenchmarkAmount), red())

Problem solved.

[JonnyPoole · Accepted solution]

You can add a Year=  modifier to your set analysis in the sum() function in the color expression.

Year= P( {<User = {'User A'}>}  Year)

This should narrow the calculation to include only Years that are associated with User A. The p() function can be used  to derive the filter based on a different SET statement.

You can make 'User A' dynamic based on a selection too if you want.

If all your expressions (color etc...) are focused on the Years for User A you shouldn't run into this issue

[#6]

As always you come up with several great answers - thx