Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
msmichael
Creator
Creator

set analysis not working

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_IDGA LAST_UPDATED
27112/10/2016 3:45:12 AM
27110/22/2018 10:55:12 AM
27111/5/2018 6:08:39 PM
2848/9/2018 3:36:03 PM
28411/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

 

Labels (2)
1 Solution

Accepted Solutions
vhespinog
Contributor III
Contributor III

Hi, may be this

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

 

View solution in original post

3 Replies
vhespinog
Contributor III
Contributor III

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
Creator
Creator
Author

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
Contributor III
Contributor III

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