Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

ederleelefon_gt
New Contributor II

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

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

Try this

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

4 Replies
duncanblaine
Contributor

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

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.

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

Try this

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

ederleelefon_gt
New Contributor II

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

Hi Sunny this works like a charm, thank you,

It is now giving me the correct ranges

Between Correct.png

ederleelefon_gt
New Contributor II

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

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

Community Browser