Dates in SetAnalysis

[morenoju]

Hi,

I keep trying to figure out the correct way to deal with dates in set analysis. This time I'm trying to calculate the average for values corresponding to the last 52 weeks.

I thought that a >= condition to start from the week 364 days ago and a <= condition for today's week would be enough:

Avg({<start_date={">=$(WeekStart(Date(Today()-364,'MM/DD/YYYY')))"}, start_date={"<=$(WeekStart(Date(Today(),'MM/DD/YYYY')))"}, event_status_id={'31'}>}verification_time)

It doesn't seem to be working since I can see that removing the two start_date conditions, I keep getting the same results in the chart. Qlik Sense is not notifying of any syntax error, but in previous times I've got recommendations from users in this community to slightly modify the expression and make it work.

Any idea?

Thanks much

[sunny_talwar · Accepted solution]

You cannot have two set analysis on a same field... you need to combine the set analysis for start_date

Avg({<start_date = {">=$(=Date(WeekStart(Today()-364), 'MM/DD/YYYY'))<=$(=Date(WeekStart(Today()), 'MM/DD/YYYY'))"}, event_status_id={'31'}>} verification_time)

[morenoju]

Hi Sunny, I didn't know of that limitation. No problem, then. I'll use your suggestion.

Thanks!

[sunny_talwar]

Forgot to mention above, but I changed 2 other things above

1) Added Date function before WeekStart.... (Date(WeekStart(...)) instead of WeekStart(Date))...

2) Added an equal sign within the dollar sign expansion....