6 Replies Latest reply: Mar 6, 2016 7:45 PM by jagan mohan rao appala RSS

    Calculation of the last past month

    André Kabalkin

      Dear experts,

       

      in my report I have to calculate "Std." for the last 3 Month --> Feb, Jan, Dez, Nov.

      I have problems by changing of the year.

      In my global variable (Variablenliste) I calculate the past month:

       

      PreviousMonth_1

      if(((num($(CurrentMonth)-1))='0' or num($(CurrentMonth))-1)='-1' or (num($(CurrentMonth)-1))='-2', num($(CurrentMonth)-1)+12, num($(CurrentMonth)-1) )

      PreviousMonth_2

      if((num(month(today())-2))='-1' or (num($(CurrentMonth)-2))='-2' or (num($(CurrentMonth)-2))='0', num($(CurrentMonth)-2)+12,num($(CurrentMonth)-2) )

      PreviousMonth_3

      if((num(month(today())-3))='-1' or (num($(CurrentMonth)-3))='-2' or (num($(CurrentMonth)-3))='0', num($(CurrentMonth)-3)+12,num($(CurrentMonth)-3) )

       

      In my diagram (pivottable) I use follow formel:

      (sum({< Month = {"$(=$(PreviousMonth_3) )"}>} Std)+

      sum({< Month = {"$(=$(PreviousMonth_2) )"}>} Std)+

      sum({< Month = {"$(=$(PreviousMonth_1) )"}>} Std))

       

      This is working wrong, because I don't change the year: =num(Year(today()))-1

       

      In my data I have records for 2015 only for November and Dezember. By other calculation I do:

      if (Year='2015',

      (sum({< Month = {"$(=max(Month))"}>} Std)), 

      (sum({< Month = {"$(=min(Month)+11)"}>} Std))

      )

       

      How can I calculate the sum of the last 3 month including change of the year?

      Do you have another suggestions ?

       

      Kind regards,