6 Replies Latest reply: Apr 8, 2015 9:09 AM by Sunny Talwar RSS

    Using date ranges in set analysis

    David Brandstädter

      Hi all,

       

      I'm struggeling using a specific date range for a KPI object:

       

      I want to calculate the sum of sales of the last 30 days. Date is stored in a dimension called [Booking Date] and the sales amount is stored in [Amount in Euro].

      Now, let's assume my newest Booking Date is the 11th of November 2014, then this function gives me exactly what I am looking for:

       

      Sum({$<[Booking Date] = {">=2014-10-12<=Max([Booking Date])"}>} [Amount in Euro])

       

      Now, my issue is that I am not able to use any function for the lower bound of the function above. I tried things like

      1. Sum({$<[Booking Date] = {">=Min([Booking Date])<=Max([Booking Date])"}>} [Amount in Euro]
      2. Sum({$<[Booking Date] = {">=2014-10-12 + -30<=Max([Booking Date])"}>} [Amount in Euro]
      3. Sum({$<[Booking Date] = {">=Max([Booking Date]) - 30<=Max([Booking Date])"}>} [Amount in Euro]
      4. Sum({$<[Booking Date] = {">=Max([Booking Date]) <=Max([Booking Date])"}>} [Amount in Euro]


      All just sum up to 0. Particularly interesting is example 4. Since the boundaries are "larger or equal" and "smaller or equal" I would expect to see the sum of all entries with that particular booking date.

       

       

      Any help would be highly appreciated!

       

      Thank you