HELP! Can't subtract days from between dates in set analysis

[ederleelefon_gt]

Hi All,

I hope you can help me I was stuck on this for quite so many weeks,

I was trying to get the range of Sales using Between in Set Analysis,

Sum({$<Date={">=$(=max(Date)-7)<$(=max(Date))"}>}Sales)

I have Table which looks like this, ideally If I was to get only those Items from 1/24/2008 to 1/17/2008 but it is still aggregating based on the last available date.

Expected Output should be: 4624+480=  5104

Thanks in advance!

[duncanblaine]

If you change your Date to being a numeric value (either directly in the table it belongs to, or indirectly in a Calendar table) in your load script:

eg. Num(Date) as Date

Then the set analysis works because it uses a numeric comparison. Your expression is correct, although I think you need <= rather than <

ie.

=Sum({<Date= {">=$(=max(Date)-7)<=$(=max(Date))"}> } Sales)

I don't think Qlik has recognised your Dates as dates.

[sunny_talwar · Accepted solution]

Try this

Sum({$<Date = {">=$(=Date(Max(Date) - 7, 'M/D/YYYY'))<=$(=Date(Max(Date), 'M/D/YYYY'))"}>} Sales)

[ederleelefon_gt]

Hi Sunny this works like a charm, thank you,

It is now giving me the correct ranges

[ederleelefon_gt]

Thank you, also take this in mind, appreciate all your help