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

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

ederleelefon_gt — Thu, 10 May 2018 01:51:16 GMT

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!

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

duncanblaine — Thu, 10 May 2018 02:26:05 GMT

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

sunny_talwar — Thu, 10 May 2018 02:34:39 GMT

Try this

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

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

ederleelefon_gt — Thu, 10 May 2018 03:31:53 GMT

Hi Sunny this works like a charm, thank you,

It is now giving me the correct ranges

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

ederleelefon_gt — Thu, 10 May 2018 03:32:35 GMT

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