Max value with only set analysis

[Anonymous]

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?

[vishsaggi]

May be try this ?

= FirstSortedValue(City, -Aggr(Max(TimeDiff), City))

[Anonymous]

Seems not to be working. Maybe as I can have a city multiple times with different TimeDiff?

[vishsaggi]

Can you share this sample data you are working on?

[Anonymous]

How about this:

=only({<TimeDiff={'$(=max(aggr(sum(TimeDiff),City)))'}>}City)

[Anonymous]

I guess it should actually be

=only({<TimeDiff={'$(=max(aggr(max(TimeDiff),City)))'}>}City)

[woshua5550]

Try this

=Only({<TimeDiff = {"$(=Time(Max(TimeDiff),'hh:mm:ss'))"}>}City)

[sunny_talwar]

How about this

=Concat({$<TimeDiff={'$(=max(TimeDiff))'}>}City)

or

=Concat({$<TimeDiff={'$(=Interval(max(TimeDiff)))'}>}City)

[Anonymous]

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)

[sunny_talwar · Accepted solution]

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)