Expression Background Colour Query

[Anonymous]

Hello.

I have a pivot table which measures the performance of various kpis nice and simple:

Note I have fixed the date dimensions to the last 6 relative months:

I now want to shade the background of the expressions based on the actual performance relative to the threshhold:

This works sort of but for a reason I sort of understand but cannot fix it adds colours to all the months which are not in the set expression.

I have set my background colour using the expression not in the visual cues tab as such:

=IF(

SUM({$<MEASURE_CAL.RelativeMonth={'-5','-4','-3','-2','-1','0'},[Measure Outcome]={'Passed'},[Measure Type]={'KPI'}>}#_MEASUREVOL)

/

SUM({$<MEASURE_CAL.RelativeMonth={'-5','-4','-3','-2','-1','0'},[Measure Type]={'KPI'}>}#_MEASUREVOL)

>=[Measure Threshold],AROlive,

IF(

SUM({$<MEASURE_CAL.RelativeMonth={'-5','-4','-3','-2','-1','0'},[Measure Outcome]={'Passed'},[Measure Type]={'KPI'}>}#_MEASUREVOL)

/

SUM({$<MEASURE_CAL.RelativeMonth={'-5','-4','-3','-2','-1','0'},[Measure Type]={'KPI'}>}#_MEASUREVOL)

>=([Measure Threshold]-0.05),ARSun,ARRed))

II think even though I am limiting the set the conditional expression returns ARRed for everything including the missing months and measure type which are excluded by the set.

I can solve this easily by having a calculated dimension or by adding a new month dimension which just contains the last 6 months in the script but this is not ideal for various reasons.

Any ideas?

[neelamsaroha157]

Would you be able to share a sample file.

[sunny_talwar]

What exactly is the issue? That the color is showing up for the missing value month (2018-Feb)?

[Anonymous]

Yes the data set goes back about ten years, the visualisation only needs to go back 6 months so the problem is that as soon as I apply the colour function it shows all the months back to 2008.

Another way to get rid of the problem is to tick the suppress zero voles option but as I'm presenting percentages I need to show 0% performance. WHich gives me an idea in that I could fudge it by adding 0.00001 to each result but again not ideal.

There is a need to keep all data in the application so I cant just limit it on load.

[Anonymous]

I'll add a dummy app in a bit showing the problem and the now I think fudged solution.