topic date comparison in set analysis does not work in QlikView

date comparison in set analysis does not work

msmichael — Wed, 07 Nov 2018 16:13:40 GMT

Hi expert,

the structure of my raw data is like below,

Component_ID, Env, Last Updated

1, UDB, '2018-1-1 12:25AM'

1, TST, '2018-2-1 00:15AM'

2, ABC, '2018-10-1 13:15'

I have a calendar on my dashboard so users can select a date and get the latest Env for each component before the selected date.

I created a straight table, the dimension is "Component_ID", added one expression like below:

=FirstSortedValue( {<[Report Component Audit Last Updated]={"<=$(=vDate)"}>} [Report Component Audit ENV], -[Report Component Audit Last Updated])

the vDate is a variable I created and linked to the calendar.

However, this gives me nothing in the table. Am I doing anything wrong?

I attached my qvw file. Please help!

Thanks,

Michael

Re: date comparison in set analysis does not work

neelamsaroha157 — Wed, 07 Nov 2018 16:18:07 GMT

One of the reason is that your vDate variable date format and your fie;d format are not same.

Re: date comparison in set analysis does not work

msmichael — Wed, 07 Nov 2018 16:51:18 GMT

Hi Neelam,

Thanks for your reply.

I changed my expression like below, and it works now.

=FirstSortedValue( {<[Report Component Audit Last Updated]={"<=$(=timestamp(vDate))"}>} [Report Component Audit ENV], -[Report Component Audit Last Updated])

I am still confused though, my field type is numeric and timestamp. the vDate should be a date which is numeric also, right? and I'm using "smaller than" (<=) in my expression to compare two numeric values, why it does not work?

Thanks,

Michael