set analysis based on date logic not working as coded

dadumas — Thu, 05 Mar 2015 15:09:05 GMT

I have set analysis coded on a variable below. In a chart I have selections based on [Calendar Date]. [Entry Date] in the fact records join to [Calendar Date] in the calendar table. I wish to limit my results to records where [Target Resolve Date Time] is less than or equal to the max [Calendar Date] selected from the calendar. I then use max([Calendar Date]) in my code to achieve this. [Target Resolve Date Time] is a datetime attribute of the fact record and does not join to the calendar.

This below produces a correct result except that it does not take into account the needed Target Resolve Date logic:

sum({$<[DateType={'Entry'}>} [Case Count])

This below should work, but does not: It produces a 0 result. I added the +1 to ensure that I get records to include an additional (next) day. I have confirmed that there are severl facts records for data to support the added clause:

sum({$< [DateType='Entry'], [Target Resolve Date Time]={"<=$(=max([Calendar Date])+1)"} >} [Case Count])

If the max([Calendar Date]) returns 2/27/2015, (for the fact data that displays in the chart), BUT I my calendar selection list has "Feb-2015" selected, I wish to include 2/28/2015 in my set analysis. If gets filtered out because there is no fact data on 2/28/2015. However I still expect to return a value greater than 0.

Any ideas on what I may be doing wrong?

Re: set analysis based on date logic not working as coded

dadumas — Thu, 05 Mar 2015 16:02:18 GMT

I got rid of the = in front of max() and it worked. this below works:
sum({$< [DateType='Entry'], [Target Resolve Date Time]={"<=$(max([Calendar Date])+1)"} >} [Case Count])

I had thought that expressions require the = in front of them when used in set analysis. I guess not.

topic Re: set analysis based on date logic not working as coded in QlikView

set analysis based on date logic not working as coded

Re: set analysis based on date logic not working as coded