Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Leverage your QlikView investment to modernize BI – see how! Join Group
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Best of prior months...

I'm working on an incentive summary document, and one of the required features has me stumped.  Specifically, I need to be able to color code a table field based on whether it has matched or improved upon the best of the prior months' values and a fixed baseline (say 500).

So, if I have the following table:




The final grid would need to look something like this:

UnitJan Total
Feb TotalMar Total

In January, Something met/beat the baseline, and there were no prior months, so it gets flagged green (good!).

In Feburay, Something met the baseline (good), but failed to meet the best value from the prior months (bad), so it's red (bad).

In March, Something met the baseline (good), and met or beat the best value from the prior months (good), so it's green (good!).

In January Nothing failed to meet/beat the baseline, so it gets flagged red (bad).

In February, they beat the baseline (good), and the best prior month value (good), so it gets flagged green (good!).

In March, they meet the baseline (good), but failed to meet the best value from the prior months (bad), so it's red (bad).

Does anyone have any idea how to write the expression to set the threshold for the Visual Cues tab so I can do this?  I'm suspecting it'll involve the use of the Aggr function and/or building a summary table (which I've already done for one set of measurements), but I haven't been able to get it working yet.

1 Reply
Not applicable

Re: Best of prior months...

Oh, and I'm currently using QV 10, and the expression needs to get the best of *all* prior month values, even when a date range has been selected.  The document is only loading one year of data, so there's no need to worry about comparing to data from the prior year.