5 Replies Latest reply: Apr 8, 2014 8:22 AM by Almamy DIABY RSS

    Set Analysis Expression for previous 3 months

    Vidhya Vk


      Hi all,

       

      I want to have set analysis expression for the following scenario.

      We are now in the month of April,

      I have to show now previous 3 months (Jan, Feb, Mar) Bookings.

      when we move to May, automatically (Feb, Mar, Apr) should be displayed.

      Hardcoding (sum ({<Year={'2014'}, Month={'Jan','Feb','Mar'}[Bookings])  ) is not allowed since i am looking expression for the generation of pdf report.

      i tried the following expression

        sum({<Year={'2014'},
      [Date formatted]={">=$(AddMonths(YearStart($(vDate)),Month($(vDate))-4))"}* {"<=$(Date(MonthStart($(vDate))-1))"}}>}[Booking])

      but i am getting YTD, which i am not looking for..

      If i give "AddMonths(YearStart($(vDate)),Month($(vDate))-4))" in a text box I am getting Jan2014 and

      if i give "Date(MonthStart($(vDate))-1))" am getting Mar 2014, which is what i want.

      but when I combine them and give them as expression  in a pivot table as shown above, I am getting YTD

      I want to display the data from Jan 2014 to Mar 2014, since we are in April.

       

      Hope my query is clear.

      Kindly help me on this issue.

      Thanks in advance

      Vidhya