Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ederleelefon_gt
Partner - Contributor III
Partner - Contributor III

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

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

Between.png

Thanks in advance!

1 Solution

Accepted Solutions
sunny_talwar

Try this

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

View solution in original post

4 Replies
duncanblaine
Creator
Creator

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

Try this

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

ederleelefon_gt
Partner - Contributor III
Partner - Contributor III
Author

Hi Sunny this works like a charm, thank you,

It is now giving me the correct ranges

Between Correct.png

ederleelefon_gt
Partner - Contributor III
Partner - Contributor III
Author

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