Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

msmichael
Contributor

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

Re: set analysis not working

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

Re: set analysis not working

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

msmichael
Contributor

Re: set analysis not working

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

Re: set analysis not working

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