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

# Date calculations

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

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

Hello, Please find attached dummy data. Thank you.

• ###### 3. Re: Date calculations

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.

• ###### 4. Re: Date calculations

• ###### 5. Re: Date calculations

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

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.