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:
- How exactly can I calculate prior quarter from the current date?
- 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