1 Reply Latest reply: Sep 28, 2013 1:56 AM by Evgeniy.Rasyuk RSS

    MTD formula

    user8927833

      out time dimension looks like this

       

      Time:>FY:>Q1:>Month:>Weeks

      in below formula we calculating Week to date at MTD members

       

      we are removing YTD hierarchy that means we will  not have YTD(Oct), YTD(Nov)....

       

      Below formula will not work once we remove YTD. I was playing with Currmbrrange but was not able to ristrict it to select the children of the selected month

       

      Jan works fine with no issues but for Feb i tried using @SUMRANGE ("W_T_D", @CURRMBRRANGE ("Time", LEV, 0,,0)) but it starts from first week of jan . IS there any way i can start the range from Feb first week instead of Jan first week for the month of feb . Likewise for other months

       

      i tried using -1 and 1 then it moved one member ahead and behind . would not give the desired result

       

      IF(@ISGEN("Time", 5))

      IF(@ISDESC("Jan"))

        @SUMRANGE ("W_T_D", @CURRMBRRANGE ("Time", LEV, 0,,0));

      ELSEIF(@ISDESC("Feb"))

        @SUMRANGE ("W_T_D", @CURRMBRRANGE ("Time", LEV, 0,,0)) - "YTD(Jan)"->"W_T_D";

      ELSEIF(@ISDESC("Mar"))

        @SUMRANGE ("W_T_D", @CURRMBRRANGE ("Time", LEV, 0,,0)) - "YTD(Feb)"->"W_T_D";

      ....

       

       

      Any information of this will be really helpful for me

       

      thank you so much