Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Max value with only set analysis

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 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

10 Replies
vishsaggi
Champion III
Champion III

May be try this ?

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

Anonymous
Not applicable
Author

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

vishsaggi
Champion III
Champion III

Can you share this sample data you are working on?

Anonymous
Not applicable
Author

How about this:


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



Anonymous
Not applicable
Author

I guess it should actually be

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

woshua5550
Creator III
Creator III

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
Not applicable
Author

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

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)