Set Analysis >= Not Working

[#1]

I am trying to do a comparison on some dates. Because of the date formatting differences I made the dates a NUM with the following load scripts:

NUM(date(Date#(LastActivityDate,'YYMMDD'),'YYYYMMDD')) as CalendarDateKey1,

NUM(date(floor(ResponseDate),'YYYYMMDD')) as CalendarDateKey

I have created a pivot table chart that looks like the following:

The problem is the Issues column. In the expressions, I have the following definition for Issues:

Count({$<QvsI = {'I'}, CalendarDateKey={"<=CalendarDateKey1"}>}QvsI).

I can't figure out why it won't add a 1 to the Issues column for this account. The CalendarDateKey is < CalendarDateKey1.

[vgutkovsky · Accepted solution]

Well, set analysis doesn't really work that way--you can't use it to check which dimensional value you're currently in. So your expression is actually trying to check whether CalendarDateKey is less than or equal to CalendarDateKey1 as a whole, rather than that specific instance. You would need to use an IF statement in combination with set analysis: count({<Qvsl={'I'}>} if(CalendarDateKey<=CalendarDateKey1,Qvsl))

Regards,

Vlad

[#3]

Thank you so much for the explanation. This works.