Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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:

UnitMonthValue
SomethingJan

500

SomethingJan100
NothingJan300
NothingJan50
SomethingFeb400
SomethingFeb100
NothingFeb500
NothingFeb150
SomethingMar600
NothingMar500

The final grid would need to look something like this:

UnitJan Total
Feb TotalMar Total
Something600500600
Nothing350650500

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.

Community Browser