6 Replies Latest reply on Aug 7, 2017 2:29 AM by Chris Geatch

    Date calculations

    shwetha sarma

      I have data for last 2 years. I want to extract last 2 days, last 7 days(without including last 2 days) and last one month (without including last weeks) data.

       

      Today is 08/04/2017. So, I want to calculate last 2 days i.e., Aug 2.

      Dates between July 26- Aug 2  and June 26-July 26. I want these calculations to be dynamic. The dates I have given are only for example purpose.

       

      I do not have any dummy data for this. I would like the logic.

       

      Thanks,

        • 1. Re: Date calculations
          Catherine Brown

          It would really help people, help you if you did provide some sort of dummy data.  For the most part, anyone helping you would be taking a few minutes of their time to generate some sort of set, that you likely already have (or could take a few minutes of your own time to generate or randomize).  The best way to test any logic, is with data.

          • 2. Re: Date calculations
            shwetha sarma

            Hello, Please find attached dummy data. Thank you.

            • 3. Re: Date calculations
              Jayson Osmars

              I used a formula like this to bucket date ranges into separate calculated measures.

               

              [3MPY$]:

              sum(

              IF ({FIXED :DATEADD('month',-12,Max([Date]))} >= [Date]) and ({FIXED: DATEADD('month',-14,[Max Date])} <= [Date]) Then

                   [Sales]

              else 0

              end)

               

              You'll need to adjust the periods needed and duplicate for the other periods.

               

              Hope it helps Shwetha.

              • 5. Re: Date calculations
                Catherine Brown

                If you are interested in showing something like Sales, perhaps the attached will help. I took your dummy set and for every row put 100 units.

                 

                The calculations are all related to the today() function.

                 

                Last 2 Days: if datediff('day',[Purch Dt],today())<=2 then [Amount] end

                Last Week: if datediff('week',[Purch Dt],today())<=1 then [Amount] end

                Last week, without the first 2 days: sum([Last Week]) - sum([Last 2 Days])

                Last Month: if datediff('month',[Purch Dt],today())<=1 then [Amount] end

                Last month without first week: sum([Last Month]) - sum([Last Week])

                 

                If you are wanting these to be line graphs, rather than just totals, than you'll need to do something else.

                1 of 1 people found this helpful
                • 6. Re: Date calculations
                  Chris Geatch

                  You could also approach it from the other side, by re-categorising each date into your desired groupings:

                   

                  IF DATEDIFF('day',[PURCH_DT],today())<= 2 THEN "Last 2 Days"

                  ELSEIF DATEDIFF('day',[PURCH_DT],today()) <= 7 THEN "Last Week"

                  ELSEIF DATEDIFF('day',[PURCH_DT],today()) <= 28 THEN "Last Month"

                  ELSE "Earlier"

                  END

                   

                  Then you can draw charts, perform calculations etc. based on the new dimension, rather than having to try to work with dates in your measure calculations.