Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
darren_hankinso
Contributor II
Contributor II

Expression Background Colour Query

Hello.

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

standard.PNG

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:

issue.PNG

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?

4 Replies
neelamsaroha157
Specialist II
Specialist II

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

darren_hankinso
Contributor II
Contributor II
Author

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.

darren_hankinso
Contributor II
Contributor II
Author

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