Check last 7-28-90-365 days and calculate

[JpChapron]

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 · Accepted solution]

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!

[WillowMobileSys · Accepted solution]

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!

[JpChapron]

Hello ! me again ! 

Just had a though that bugs me quite a bit.

So in the case of your answer, it would always check relative to TODAY(). however, if I make an entry lets say for the previous month (user forgot to enter data maybe) I want this data to check relative to the date he enter the data from (in this case [Duty_Date]). So I thought I'll just change Today() to [Duty_date]: 

Last 7 days =  AND( [Duty_Date] >= [Duty_Date] - 7, [Duty_Date] <= [Duty_Date])

Seems my thinking is wrong because, that doesnt work

what do you think? 

[JpChapron]

Thank you ! this helps a lot ! 

[WillowMobileSys · Accepted solution]

---

@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. 

[JpChapron]

Well yes and no,

I need the Active row to check the previous 7,28,90 and 365 days against the date, and compare that another collumn doesnt go over a certain amount in a SUM() Expression,

I'm adding my "working but not working" expression :

IF(AND([Duty_Date] >= TODAY() - 7, [Duty_Date] <= TODAY())
,
[Duty_TimeTotal] +
SUM ( 
SELECT (Duty_data[Duty_TimeTotal], LOOKUP(USEREMAIL(),Pilote,Pilote_Email,Pilote_License) = [_THISROW].[Duty_Pilot]))
,
[Duty_TimeTotal]
)

 The Expression checks the data for this user only, and gives me the sum of [Duty_TimeTotal] for this period including the value added from the active row

EDIT : Thinking about what a wrote, maybe I should ask differently. How basically how would you go about checking if value is TRUE in the past 7 days from the Date in the form you're entering

something like : Is the value TRUE shown in the records for the past 7 days ?