Set Analysis between two dates

[swearengen]

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

[marcus_sommer · Accepted solution]

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 · Accepted solution]

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.