Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

morenoju
Contributor II

Dates in SetAnalysis

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Dates in SetAnalysis

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)

3 Replies
MVP
MVP

Re: Dates in SetAnalysis

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
Contributor II

Re: Dates in SetAnalysis

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

Thanks!

MVP
MVP

Re: Dates in SetAnalysis

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