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

AppSheet AppSheet Q&A
,
1

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

2

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!

2 Likes
3

Thank you ! this helps a lot !

2 Likes
4

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?

5

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.

1 Like
6

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 ?