topic Re: set analysis not working in QlikView

set analysis not working

msmichael — Sat, 16 Nov 2024 02:11:02 GMT

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

Re: set analysis not working

vhespinog — Fri, 18 Oct 2019 20:53:32 GMT

Hi, may be this

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

Re: set analysis not working

msmichael — Fri, 18 Oct 2019 22:05:25 GMT

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?

Re: set analysis not working

vhespinog — Fri, 18 Oct 2019 22:25:59 GMT

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