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

      Hello,

       

      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.