topic Re: Max value with only set analysis in QlikView

Max value with only set analysis

Anonymous — Tue, 14 Nov 2017 20:21:09 GMT

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?

Re: Max value with only set analysis

vishsaggi — Tue, 14 Nov 2017 20:26:50 GMT

May be try this ?

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

Re: Max value with only set analysis

Anonymous — Tue, 14 Nov 2017 20:31:31 GMT

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

Re: Max value with only set analysis

vishsaggi — Tue, 14 Nov 2017 20:33:31 GMT

Can you share this sample data you are working on?

Re: Max value with only set analysis

Anonymous — Tue, 14 Nov 2017 22:39:59 GMT

How about this:

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

Re: Max value with only set analysis

Anonymous — Tue, 14 Nov 2017 22:42:57 GMT

I guess it should actually be

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

Re: Max value with only set analysis

woshua5550 — Wed, 15 Nov 2017 04:26:58 GMT

Try this

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

Re: Max value with only set analysis

sunny_talwar — Wed, 15 Nov 2017 09:27:13 GMT

How about this

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

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

Re: Max value with only set analysis

Anonymous — Wed, 15 Nov 2017 15:02:50 GMT

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)

Re: Max value with only set analysis

sunny_talwar — Wed, 15 Nov 2017 15:16:22 GMT

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)

Re: Max value with only set analysis

Anonymous — Wed, 15 Nov 2017 16:24:08 GMT

That was the solution! Thank you!