7 Replies Latest reply on Apr 7, 2016 12:12 PM by Shiva Prakash Y V

    Calculate a Date Field  to count the last 7 days, last 30 days,..

    cyber s

      Hi All,

       

      My data is as shown in the attachment.

       

      I need to calculate the average and the Output should be

       

         Date         Profit

      12/9/2013       3

      12/8/2013       5

      12/7/2013       2

      12/6/2013       5

      12/5/2013       7

      12/4/2013       5

      12/3/2013       7

      Last 7 Days    4.8 (i.e. (3+5+2+5+7+5+7)/7)

      Last 14 Days   5.1 (i.e. (3+5+2+5+7+5+7+4+7+4+7+8+5+3)/14)

      Last 30 days   5.9 (i.e (3+5+2+5+7+5+7+4+7+4+7+8+5+3+6+8+9+4+8+9+4+8+9+4+5+3+5+7+8+9)/30

       

      For this created a calculated field for the date as:

       

      if ([Order_Date]>DATEADD('day', -7,Today()) AND

      Order_Date<Today())) then Left(str(Order_Date),11)

      elseif Order_Date>DATEADD('day', -14,Today()) and Order_Date<Today())) then "Last 14 days"

      elseif Order_Date>DATEADD('day', -30,Today()) and Order_Date<Today())) then

      "Last 30 days"

      end

       

      But this doesn't work properly.

       

      Any help,