9 Replies Latest reply on Aug 23, 2018 7:34 PM by tian.yang.4

    how to get 4-week average?

    tian.yang.4

      Hi team,

       

      I want to identify today, yesterday, last week and four-week average.

      I now use following formula to describe today, yesterday and last week, so I can get values(sales, profits) accordingly.

      But I have no idea how to describe four-week average.

      supposing today is Aug 21, I want value of ( Aug 21 + Aug 14 +Aug 7 +July 31)/4

       

       

      IF DATEDIFF('day',DATETRUNC('day’,[DATE]),

       

      {MAX(DATETRUNC('day’,[DATE]))}

       

      )=0 THEN ‘TODAY’

       

      ELSEIF

       

      DATEDIFF('day',DATETRUNC('day’,[DATE]),

       

      {MAX(DATETRUNC('day’,[DATE]))}

       

      )=1 THEN ‘YEASTERDAY’

       

      ELSEIF DATEDIFF('day',DATETRUNC('day’,[DATE]),

       

      {MAX(DATETRUNC('day’,[DATE]))}

       

      )=7 THEN ‘LAST WEEK’

       

      END

       

      Thank in advance

      YT