5 Replies Latest reply on Apr 20, 2018 7:37 PM by Diogo Braga

# Calculate date difference between current and 7 days prior

I have the current total applications per zip code for each school, I need a calculated field showing the total applications 7 days prior to the current date. I can't be a table calculation, given the way I want to create the visual.

This is what I have so far:

{ FIXED [Academic Year],[School Code],[Zip]: IF MAX([Apptime])=MAX([Apptime])-7 then COUNTD([App ID Modified]) end}

Workbook attached. Thank you!

• ###### 1. Re: Calculate date difference between current and 7 days prior

Hi Diogo,

What do you mean by current date?

Is it today()?  or latest date in the dataset?

Thanks,

Shin

• ###### 2. Re: Calculate date difference between current and 7 days prior

The latest in the dataset!

• ###### 3. Re: Calculate date difference between current and 7 days prior

Did both cases.

Case of the latest date in data.

Case of today()  == might be same now.?

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: Calculate date difference between current and 7 days prior

It turns out I need a LOD for the current (latest in the dataset), I tried using the 7 days prior calcs as a base but it didn't work. What am I doing wrong?

7 days prior calculation:

{ FIXED [Academic Year],[School Code],[Zip]: countd(if {fixed:MAX([Application Date])}-7 >=[Application Date] then [App ID Modified] end)}

What I tried for the current (latest in the dataset):

{ FIXED [Academic Year],[School Code],[Zip]: countd(if {fixed:MAX([Application Date])}=[Application Date] then [App ID Modified] end)}

• ###### 5. Re: Calculate date difference between current and 7 days prior

This worked! If there's a better approach, please share!

{ FIXED [Academic Year],[School Code],[Zip]: countd(if {fixed:MAX([Application Date])}-0 >=[Application Date] then [App ID Modified] end)}