topic DateTime Comparison in Set Analysis in QlikView

DateTime Comparison in Set Analysis

Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I have scenario where I have 2 DateTime fields and I would like to compare the two to retrieve a value. I tried set analysis but it doesn't seem to work.

In this case I only want to return the latest "Card_Allocation_DateTime" where "Shift_DateTime" is greater than "Card_Allocation_DateTime". How can this be done?

Please see my qvw and spreadsheet (with source data and desired results)

Re: DateTime Comparison in Set Analysis

Anonymous — Fri, 03 Jun 2016 11:51:56 GMT

Hi Tumelo,

This is better to do with If: Max(TOTAL <Card_ID,Shift_DateTime> If(Shift_DateTime>Card_Allocation_DateTime, Card_Allocation_DateTime))

Kind regards!

Re: DateTime Comparison in Set Analysis

Fri, 03 Jun 2016 11:58:38 GMT

Hi Manuel

This doesn't work either, can you please attach an app?

Re: DateTime Comparison in Set Analysis

Anonymous — Fri, 03 Jun 2016 12:00:45 GMT

Hi Tumelo,

See attached file.

Regards

Re: DateTime Comparison in Set Analysis

Fri, 03 Jun 2016 12:10:24 GMT

Thanks for the reply manuel

But the expression returns Null. I would like it to give me something like this...

Shift_DateTime	Card_ID	Card_AllocationStatus	Card_AllocationReason	Card_Allocation_DateTime
2014/07/08 12:58:23	7440	2	Card Allocated	2016/03/27 17:03:25

Re: DateTime Comparison in Set Analysis

rubenmarin — Fri, 03 Jun 2016 12:17:00 GMT

Hi Tumelo, can you try this expression?

If(Card_Allocation_DateTime=Max(TOTAL <Card_ID> Card_Allocation_DateTime)

and Shift_DateTime<Max(TOTAL <Card_ID> Card_Allocation_DateTime), Max(TOTAL <Card_ID> Card_Allocation_DateTime))

Re: DateTime Comparison in Set Analysis

Anonymous — Fri, 03 Jun 2016 12:22:13 GMT

Returns Null because all dates on Card_Allocation_DateTime are greater of Shift_DateTime, there aren't any date where Shift_DateTime > Card_Allocation_DateTime.

If you want to show last Card_Allocation_DateTime > Shift_DateTime, then Max(TOTAL <Card_ID,Shift_DateTime> If(Shift_DateTime<Card_Allocation_DateTime, Card_Allocation_DateTime)).

Your requirement say: return the latest "Card_Allocation_DateTime" where "Shift_DateTime" is greater than "Card_Allocation_DateTime", and you want: return the latest "Card_Allocation_DateTime" where "Card_Allocation_DateTime" is greater than "Shift_DateTime"

Regards!

Re: DateTime Comparison in Set Analysis

Fri, 03 Jun 2016 12:36:28 GMT

Thanks Ruben, this is perfect

Re: DateTime Comparison in Set Analysis

Fri, 03 Jun 2016 12:42:36 GMT

Sorry Manuel, I didn't ask correctly but thanks a lot for your help