7 Replies Latest reply: Jul 13, 2017 9:35 AM by Sunny Talwar RSS

    Use Date range in SET Analysis, yet ignore a Date selection

    Mike Lutomski



      I need some help with some SET Analysis.


      I'm creating a KPI to sum a value for the most recent 3 months that cannot change, and then I want another KPI that shows the sum of the same value for a 3 month date range a user selects.


      1st KPI

      Sales = $3.5M for Feb, Mar, Apr


      2nd KPI

      Sales = $1.1M for Jan, Feb, Mar (3 months based on date a user selects).


      When the user selects Mar, I want the 1st KPI to stay the same.


      My Date field is formatted as M/D/YYYY


      Here is my code:


      Sum({$<[Date] ={'>=$(=AddMonths(Max([Date]),-3))<=$(=Max([Date]))'}, [Entity Category] = {'Sales'}>} [Amount])


      I've tried entering the "[Date] = ", but this then cancels out my initial date range above.


      I've tried entering the "[Date] = e([Date])", but this only excludes the date that is selected.


      Any help would be appreciated.