11 Replies Latest reply on Aug 6, 2018 7:22 PM by Sonali chavan

    Rolling 4 and 12 weeks

    Sonali chavan

      Hi Guys,

       

      I am struggling to achieve rolling 4 and 12 weeks from selected date:

      I have written this formula:

       

      IF [Parameters].[KPI]='Roll 4 week'

      then

      INT([Week Number]) <= DATEPART('week',[Date Selected])and

      INT([Week Number]) >=    DATEPART('week', DATEADD('week',-3,[Date Selected]))

      ELSEIF[Parameters].[KPI]='Roll 12 week' THEN

      INT([Week Number]) <= DATEPART('week',[Date Selected])and

      INT([Week Number]) >=    DATEPART('week', DATEADD('week',-11,[Date Selected]))

       

      where week number is number of week.(1,2...52)

       

      my formula fails when user selects 1st 3 weeks from start of the week.

       

      Ex. When user selects 1 jan 2018 than it should give me 1, 52,51, and 50 week , same is the case with 2nd week (2,1,52,51)

       

      Somehow i am not able to achieve this. Can someone please suggest something.

       

       

      Regards,

      Sonali.