Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
swearengen
Contributor III
Contributor III

Set Analysis between two dates

Hi,

I'm actually going mad at this stage trying to figure this out. I'm trying to build a set condition that will just include where a date is between two other dates.  I've tried both of the below 

FirstSortedValue({<event_date={">=$(=min(StartDate))<=$(=max(EndDate))"}}bus_outcm_cd, -event_ts)

FirstSortedValue({<event_date = {"$(='>=' & Date(Min(StartDate)))"}, event_date = {"$(='<=' & Date(Max(EndDate)))"}}bus_outcm_cd, -event_ts)

I've tried every combination of Date, Date#, Floor etc to try and see why the comparisons aren't working. I then added expressions in to a table to make sure i could actually tell the difference between two dates. As you'll see from the attached it can tell when a date is in range but the set condition is showing no results for bus_outcm_cd.

I don't know if it's relevant but Date# is returning a date rather than a number.

I got this functionality working on other dashboards but I can't for the life of me figure this one out. Any pointers gratefully received.

Thanks,

 

Stephen

 

Labels (1)
2 Solutions

Accepted Solutions
marcus_sommer

There is a small syntax issue because one bracket is missing (here in bold):

FirstSortedValue({<event_date={">=$(=min(StartDate))<=$(=max(EndDate))"}>} bus_outcm_cd, -event_ts)

Beside this you used firstsortedvalue() which returned only a value if this value is unique - if there are multiple ones it returns NULL. This must not to be the issue here but to ensure that the set analysis is working like expected I suggest changing it to a count() or concat().

Further relevant is that your applied $-sign expansion $(=) creates an adhoc-variable which is calculated globally before the chart is evaluated and the single result is applied to all rows. So this means it's not calculated within the context of the object and should your results be needed in respect to the rows you couldn't apply such logic else you need to use an if-loop.

- Marcus

View solution in original post

swearengen
Contributor III
Contributor III
Author

Thanks for taking the time to reply Marcus. You helped me find the root cause.

The syntax error was just a typo that snuck into my post. The actual cause of the problem was the non-unique nature of the value being returned. As it's a timestamp I was expecting unique values, however an error in a different field in the source table was causing duplicate rows. Once I allowed for this - problem solved.

Thanks again.

View solution in original post

2 Replies
marcus_sommer

There is a small syntax issue because one bracket is missing (here in bold):

FirstSortedValue({<event_date={">=$(=min(StartDate))<=$(=max(EndDate))"}>} bus_outcm_cd, -event_ts)

Beside this you used firstsortedvalue() which returned only a value if this value is unique - if there are multiple ones it returns NULL. This must not to be the issue here but to ensure that the set analysis is working like expected I suggest changing it to a count() or concat().

Further relevant is that your applied $-sign expansion $(=) creates an adhoc-variable which is calculated globally before the chart is evaluated and the single result is applied to all rows. So this means it's not calculated within the context of the object and should your results be needed in respect to the rows you couldn't apply such logic else you need to use an if-loop.

- Marcus

swearengen
Contributor III
Contributor III
Author

Thanks for taking the time to reply Marcus. You helped me find the root cause.

The syntax error was just a typo that snuck into my post. The actual cause of the problem was the non-unique nature of the value being returned. As it's a timestamp I was expecting unique values, however an error in a different field in the source table was causing duplicate rows. Once I allowed for this - problem solved.

Thanks again.