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:
| Unit | Month | Value |
|---|
| Something | Jan | 500 |
| Something | Jan | 100 |
| Nothing | Jan | 300 |
| Nothing | Jan | 50 |
| Something | Feb | 400 |
| Something | Feb | 100 |
| Nothing | Feb | 500 |
| Nothing | Feb | 150 |
| Something | Mar | 600 |
| Nothing | Mar | 500 |
The final grid would need to look something like this:
| Unit | Jan Total
| Feb Total | Mar Total
|
|---|
| Something | 600 | 500 | 600 |
| Nothing | 350 | 650 | 500 |
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.
