topic FirstSortedValue issue in QlikView

FirstSortedValue issue

marikabi — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I am trying to find the week in 2016 in which the total traffic volume per country was the highest one.

To find the max traffic volume I am using:

MAX(aggr(sum({<[YEAR]={'2016'}>}[TRAFFIC]),WEEK,COUNTRY))

and in order to find the week I am using:

FirstSortedValue(WEEK, -Aggr(sum({<[YEAR]={'2016'}>}[TRAFFIC]),WEEK))

The problem is that this second function works only when a specific country is filtered, but if nothing is filtered it gives me back "-".

I would like to have all the values, without having to filter them...

Any idea?

Thanks in advance

Re: FirstSortedValue issue

vishsaggi — Fri, 20 Jan 2017 14:38:08 GMT

May be this:

= FirstSortedValue(WEEK, -Aggr(sum({<[YEAR]={'2016'}>}[TRAFFIC]), YEAR, COUNTRY, [TRAFFIC]))

Re: FirstSortedValue issue

marikabi — Fri, 20 Jan 2017 14:41:00 GMT

Hi,

it works only for few rows:

it gives me back a value for all the countries, but sometimes it is a wrong value...

Re: FirstSortedValue issue

sunny_talwar — Fri, 20 Jan 2017 14:43:16 GMT

How about this:

FirstSortedValue(WEEK, -Aggr(sum({<[YEAR]={'2016'}>} Country, [TRAFFIC]),WEEK))

Re: FirstSortedValue issue

marikabi — Fri, 20 Jan 2017 14:45:59 GMT

Hi,

the expression is OK for Qlik, but it gives me back no results, even if I filter something....

Re: FirstSortedValue issue

sunny_talwar — Fri, 20 Jan 2017 14:48:22 GMT

Would you be able to share a sample?

Re: FirstSortedValue issue

vishsaggi — Fri, 20 Jan 2017 14:50:09 GMT

Oops i missed the week May be try this:

= FirstSortedValue(WEEK, -Aggr(Only(sum({<[YEAR]={'2016'}>}[TRAFFIC])) , Country, WEEK , [TRAFFIC]))

Re: FirstSortedValue issue

marikabi — Fri, 20 Jan 2017 14:54:47 GMT

In this case no results at all all the lines gives me back this : "-"

Re: FirstSortedValue issue

marikabi — Fri, 20 Jan 2017 14:57:23 GMT

Actually I am not able to attach the qvw file.

Here is an example of what I need:

COUNTRY	TOP TRAFFIC VOLUME (min)	WEEK
A	1054	10

Having for this specific country these values:

15	91
14	208
13	103
12	64
11	30
10	1,054
9	235
8	301
7	259
6	157
5	213
4	135
3	133
2	153
1	138

Re: FirstSortedValue issue

vishsaggi — Fri, 20 Jan 2017 15:15:45 GMT

As sunny said, it would be good if you can share your sample app for a quick look.

Can you try below as well?

What is your date field in your table like,

= FirstSortedValue(WEEK, -Aggr(sum({<[YEAR]={'2016'}>}[TRAFFIC]) , Yourdatefield , WEEK , [TRAFFIC]))

Re: FirstSortedValue issue

marikabi — Fri, 20 Jan 2017 15:19:51 GMT

Actually, I can't share the application for two reasons:

1) privacy

2) the company is now discussing with the vendors to buy the client licenses and I still don't have it...

What I can't understand is why the formula I was using gave me the correct result only when filtering... Do you think it was a my coding mistake?

Re: FirstSortedValue issue

vishsaggi — Fri, 20 Jan 2017 15:24:16 GMT

We are not sure how your data model and script looks like hence asked for a sample. YOu might want to look into this to scramble your data to protect data privacy to upload.

Preparing examples for Upload - Reduction and Data Scrambling

Re: FirstSortedValue issue

Anil_Babu_Samineni — Fri, 20 Jan 2017 15:28:16 GMT

Yes, we also can understand your problem over there

1) That's why we are creating dummy data with same Fields

2) Don't require license because if you need DOC for this we can help to create Doc for you. Or atleast we can understand what you did and what we suppose to do.

Try for me using $.

FirstSortedValue(WEEK, -Aggr(sum({$<[YEAR]={'2016'}>}[TRAFFIC]),WEEK))

Learning from stalwar1‌

Re: FirstSortedValue issue

sunny_talwar — Fri, 20 Jan 2017 15:33:34 GMT

My bad, can you try this (I misplaced Country in my previous response):

FirstSortedValue(WEEK, -Aggr(sum({<[YEAR]={'2016'}>} [TRAFFIC]), Country, WEEK))

Re: FirstSortedValue issue

marikabi — Fri, 20 Jan 2017 15:37:10 GMT

Many, many, many thanks!

It works perfectly

And thanks to all of you for having helped me

Re: FirstSortedValue issue

sunny_talwar — Fri, 20 Jan 2017 16:22:05 GMT

Is it still not resolved? I thought it was?

Re: FirstSortedValue issue

marikabi — Fri, 20 Jan 2017 16:26:13 GMT

Yes it is

The moderator has approved this content with some delay...

As you can see I posted that reply right after your one ..

thanks again!!