date comparison in set analysis does not work

[msmichael]

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

[neelamsaroha157]

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

[msmichael]

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