Find Last Week Using Calculated Field

Question & Answer
,
1

I need to create a custom field that attributes the value 1 to last week on a rolling basis. I’ve looked through documentation for the available date function in QS and don’t see a good option for this.

2

Hi, We may have to use multiple functions to achieve what you are looking for.
We can use now() to get the current date and truncate that to get start of current week and then use addDateTime function to get the dates for last week. Please let me know if this helps.

ifelse(
addDateTime(-1,‘WK’,truncDate(‘WK’,now()))>=Date(use your data column)
and addDateTime(-1,‘DD’,truncDate(‘WK’,now()))<=Date((use your data column)
,1,0)

3

I’m doing something wrong with my syntax because I get a syntax error with no additional detail:

ifelse(
addDateTime(-1,‘WK’,truncDate(‘WK’,now>={day})) AND
addDateTime(-1,‘DD’,truncDate(‘WK’,now<={day}))
,1,0)```
4

Do you want to create a calculated field that returns value 1 if the date was within last calendar week and returns 0 otherwise?
Here are 3 expressions for calculated fields that you can use (I’ve separated them to make them more readable and understandable)

  • StartOfWeek with expression truncDate(‘WK’,{day})
  • StartOfCurrentWeek with expression truncDate(‘WK’, now())
  • IsLastWeek with expression ifelse(dateDiff(StartOfWeek, StartOfCurrentWeek, ‘WK’) = 1, 1, 0)
1 Like
5

Plz try this
ifelse(
addDateTime(-1,‘WK’,truncDate(‘WK’,now()))>={day}
and addDateTime(-1,‘DD’,truncDate(‘WK’,now()))<={day}
,1,0)

6

Thank you! This solved the problem.

7

how can I get the last Saturday date in a calculated field ?