Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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))
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!
Hi Manuel
This doesn't work either, can you please attach an app?
Hi Tumelo,
See attached file.
Regards
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 |
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))
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!
Thanks Ruben, this is perfect
Sorry Manuel, I didn't ask correctly but thanks a lot for your help