DateTime Comparison in Set Analysis

[#1]

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)

[Anonymous]

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!

[#3]

Hi Manuel

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

[Anonymous]

Hi Tumelo,

See attached file.

Regards

[#5]

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

[rubenmarin · Accepted solution]

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))

[Anonymous]

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!

[#8]

Thanks Ruben, this is perfect

[#9]

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