set analysis not working

[msmichael]

Hi experts,

In my qvw, there are two fields, one is %REPORT_OUTPUT_ID, and one is "GA LAST_UPDATED". For each "%REPORT_OUTPUT_ID" field there could be more than one "GA LAST_UPDATED" data.

some examples in the data:

%REPORT_OUTPUT_ID	GA LAST_UPDATED
271	12/10/2016 3:45:12 AM
271	10/22/2018 10:55:12 AM
271	11/5/2018 6:08:39 PM
284	8/9/2018 3:36:03 PM
284	11/5/2018 6:07:28 PM

In addition, I have a calendar object which points to a variable "vDate".

Now I want to find the most recent "GA LAST_UPDATED" that is smaller than vDate for each %REPORT_OUTPUT_ID,

here's my expression:

FirstSortedValue({$<[GA LAST_UPDATED]={"<=$(vDate)"}>}[GA LAST_UPDATED], -[GA LAST_UPDATED], 1)

but somehow it returns me nothing.

The [GA LAST_UPDATED] field has $numeric and $timestamp tags, and the vDate should be a number, why the set analysis is not working?

qvw is attached.

Thanks,

Michael

[vhespinog · Accepted solution]

Hi, may be this

FirstSortedValue({$<[GA LAST_UPDATED]={"<=$(=Date($(vDate),'MM/DD/YYYY hh:mm:ss TT'))"}>}[GA LAST_UPDATED], -[GA LAST_UPDATED], 1)

[msmichael]

Hi Vhespinog,

Thanks a lot, it works! 

But why the vDate has to be converted into date? "GA LAST_UPDATED" has a number tag anyway, why QV just does a number compare?

[vhespinog]

Hi, Msmichael 

i agree with you but  i only know you need to compare both dates in the same format when use set analysis.

When you don't need compare dates  with time is a good practice use "Floor" in your script, with this remove the time and all your dates are whole numbers. 

Regards