Solved: Check last 7-28-90-365 days and calculate - Page 2

JpChapron · 11-02-2022 07:23 AM

Hi all

I've started a few days ago to play with Dates information on a new app, It's a reporting app in which reports can only be made once per day, and there is collumns that (should) calculate the value of your duty time for the last 7-28-90 and 365 days

The way the data is built is that I have a collumn for each preset ([Last_7],[Last_28],etc...)

However, I can't wrap my head around how to check previous data for the number of days I have, I was able to create something like this to check for the last 7 days with the WEEKNUM() expression :

IF(WEEKNUM([Duty_Date]) = WEEKNUM(TODAY()),"Last 7","NOPE")

which returns "Last 7" when [Duty_Date] is within the last 7 days (or week in this case).

But the Number of days is a completely different beast.

I'm looking for help from this community to help me understand better

thanks

WillowMobileSys

To find data within your date ranges, I think you will want to use expressions like below or some derivative:

Last 7 days =  AND( [Date] >= TODAY() - 7, [Date] <= TODAY())
Last 28 days =  AND( [Date] >= TODAY() - 28, [Date] <= TODAY())
Last 90 days =  AND( [Date] >= TODAY() - 90, [Date] <= TODAY())
Last 365 days =  AND( [Date] >= TODAY() - 365, [Date] <= TODAY())

WEEKNUM() function basically tracks "lines" on a calendar where week 1 is the line that contains Jan 1. Each line is from Sunday to Saturday, as you would normally see on a calendar. And each date on that line will get that same Week #, This won't help for Last # days since the period could cross week numbers.

I hope this helps!

View solution in original post

WillowMobileSys

@JpChapron wrote:
Last 7 days =  AND( [Duty_Date] >= [Duty_Date] - 7, [Duty_Date] <= [Duty_Date])
Seems my thinking is wrong because, that doesnt work

Correct, that won't work. There MUST be 3 dates to properly determine if a row should be included or not:

a period Start Date - above this was TODAY() - 7
a period End Date - above this was TODAY()
the date to check - above this was [Duty_Date]

So the question is, how do you define the period Start and End Dates?

Maybe I am mis-understanding your need. In order to "calculate the value of your duty time for the last 7-28-90 and 365 days", I am understanding that you need to gather the list of rows where [Duty_Date] is within the Last 7 days, for example, and then SUM a value across those rows.

In this scenario, Last 7 days implies from Today.

View solution in original post