1 Reply Latest reply: Jan 30, 2013 5:44 PM by theo.brinkman RSS

    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.

        • 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.