0 Replies Latest reply: Jul 14, 2014 3:54 PM by Joe Hutchings RSS

    Set Analysis to compare prior quarter to current quarter based on selection

    Joe Hutchings

      I have a problem with Set Analysis that I have so far been unable to resolve.

       

      I am developing a Suppler Scorecard dashboard. The user will select a supplier(vendor) and a year (and possibly a set of quarters in that year).

       

      I would like to develop a trend graphic that compares on-time receipt from prior quarter against current quarter for the selected vendor.

       

      The problems I'm having:

       

      1. How exactly can I calculate prior quarter from the current date?
      2. How can I make the set analysis expression respect the selected vendor?

       

      Just to experiment, I have tried this expression to see if I could get the sum of all "on-time" receipts for a prior date range - but it always returns 0?

       

      =Sum({$<PROMISE_DATE={">=$(=vLast6MonthFromSelected)<=$(=vLast3MonthFromSelected)"}>}ON_TIME)

       

      vLast6MonthFromSelected = MonthStart(Today()-180)

      vLast3MonthFromSelected = =MonthStart(Today()-90)

      PROMISE_DATE = the date the order was supposed to be delivered

      ON_TIME = 1 or 0 depending if the order arrived on time or not