Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to get the city with the max timediff between two times (in script I use Interval(Scheduled_Time - Actual_Time as TimeDiff)) by using a set analysis with an Only-function. But I am getting an error with my expression:
=Only({$<TimeDiff={$(=max(TimeDiff))}>}City)
What I want to get is a textobject with the name of the city with the highest timediff.
Paris 00:07:00
Berlin 00:12:00
Amsterdam 00:03:00
Berlin 00:11:00
London 00:13:00
So, in this case I expect to see London as it as the highest timediff. How can I achieve this?
1) What is your Date field format?
2) Do you have data for today? May be you need this
=Concat({$<TimeDiff={'$(=Interval(max({<Date = {"$(=Date(Today()))"}>}TimeDiff)))'}, Date = {"$(=Date(Today()))"}>}City)
May be try this ?
= FirstSortedValue(City, -Aggr(Max(TimeDiff), City))
Seems not to be working. Maybe as I can have a city multiple times with different TimeDiff?
Can you share this sample data you are working on?
How about this:
=only({<TimeDiff={'$(=max(aggr(sum(TimeDiff),City)))'}>}City)
I guess it should actually be
=only({<TimeDiff={'$(=max(aggr(max(TimeDiff),City)))'}>}City)
Try this
=Only({<TimeDiff = {"$(=Time(Max(TimeDiff),'hh:mm:ss'))"}>}City)
How about this
=Concat({$<TimeDiff={'$(=max(TimeDiff))'}>}City)
or
=Concat({$<TimeDiff={'$(=Interval(max(TimeDiff)))'}>}City)
Hi all, many thanks for your help.
These two are working, thanks! I probably have some issues in my database which explain why all other options are not working, while they are working if I use a simplified dataset. Am currently studying why.
One more questions, I am now trying to expand this Concat set analysis with a date is date today option. This is not giving any output though (using it in a text object). Someone can see my mistake? My text object is remaining empty.
=Concat({$<TimeDiff={'$(=Interval(max(TimeDiff)))'}, Date = {"$(=Date(Today()))"}>}City)
1) What is your Date field format?
2) Do you have data for today? May be you need this
=Concat({$<TimeDiff={'$(=Interval(max({<Date = {"$(=Date(Today()))"}>}TimeDiff)))'}, Date = {"$(=Date(Today()))"}>}City)