topic Re: largest 5 values in staright table in QlikView

largest 5 values in staright table

Wed, 25 Nov 2020 16:16:04 GMT

i have a straigt table where i want to display largest 5 values

when i'm making the dimension limits to largest 5 its not working

and the values are showing like this

region	country	total
a	IND	22
b	VNM	33
c	BGD	2
c	VNM	34
d	ARE	2
d	BRN	12
d	CHN	8
d	IND	533
d	MYS	12
f	LKA	84
f	MYS	232
f	SGP	2

i want the output as below

a	IND	22
b	VNM	33
c	VNM	34
d	IND	533
f	MYS	232

Re: largest 5 values in staright table

marcus_sommer — Sun, 16 Oct 2016 15:51:29 GMT

You could try something like this:

if(max(value) = max(total <region> value), max(total <region> value), null())

and suppressing NULL within the tab presentation.

- Marcus

Re: largest 5 values in staright table

Sun, 16 Oct 2016 16:07:43 GMT

Hi Marcus can you please elobrate where should i use this expression

Re: largest 5 values in staright table

marcus_sommer — Sun, 16 Oct 2016 16:16:36 GMT

This was meant as a normal expression. If your value isn't direct a value else an aggregation over various values then you will need an additionally aggr-function within the expression like:

if(max(aggr(sum(value), region, country)) = max(total <region> aggr(sum(value), region, country)),

max(total <region> aggr(sum(value), region, country)), null())

- Marcus

Re: largest 5 values in staright table

Sun, 16 Oct 2016 16:42:25 GMT

I tried it but no luck Marcus

Re: largest 5 values in staright table

marcus_sommer — Sun, 16 Oct 2016 16:55:05 GMT

Try it at first only with expression-parts like:

max(total <region> value)

max(aggr(sum(value), region, country))

and play a bit with them - with and without total, aggr and so on to get the right results with which you could further work.

- Marcus

Re: largest 5 values in staright table

harishkumarg — Sun, 16 Oct 2016 16:55:32 GMT

Hi,

Dimensions - region,country

expression - aggr(max(total),region)

I tried the above and it worked for me.

Please let me know if you the QVW.

Regards

Harish

Re: largest 5 values in staright table

Sun, 16 Oct 2016 17:10:03 GMT

Hi Harish

=sum(TOT_) is the expression i'm using and its a different field

my dimensions are region,country

i'm getting output as below

i doont want to show the the val;ues marked in red because for a region these are not the max values we have other values which are greater than these

Re: largest 5 values in staright table

Anil_Babu_Samineni — Sun, 16 Oct 2016 17:31:48 GMT

Is This you are expecting?? Marcus Expression Should work

Re: largest 5 values in staright table

harishkumarg — Sun, 16 Oct 2016 17:36:28 GMT

If TOT_ is your field name then change your expression as aggr(max(TOT_),region)

If it is not working can you share some sample data.

Regards

Harish

Re: largest 5 values in staright table

harishkumarg — Mon, 17 Oct 2016 02:02:38 GMT

The above solution worked for you?

Regards

Harish

Re: largest 5 values in staright table

Mon, 17 Oct 2016 03:45:20 GMT

Hi hariah i will try it and let u know

Re: largest 5 values in staright table

Anonymous — Mon, 17 Oct 2016 04:37:18 GMT

Hi John,

Try following,

Dimension1-- region

Dimension2-- country

Expression--max(aggr(sum(total), region)).

Its working for me and giving following output after setting dimension limit 5

region	country	max(aggr(sum(total), region))
a	IND	22
b	VNM	33
c	BGD	36
d	ARE	567
f	LKA	318

Thanks,

Re: largest 5 values in staright table

hemachandran — Mon, 17 Oct 2016 05:19:21 GMT

Hi cena,

Try this expression,

=if(max(total) = max(total <region> total),max(total <region> total))

Re: largest 5 values in staright table

beck_bakytbek — Mon, 17 Oct 2016 08:45:50 GMT

Hi John,

try this:

Calculated Dimension: if(aggr(rank(sum(Total)),Country)<=5,Country, Null())

and dont Forget to check: Suppress When Value is Null

i hope that helps

beck

Re: largest 5 values in staright table

Mon, 17 Oct 2016 09:15:12 GMT

Hi beck it solved my problem to some extent

Still im facing some issue

Re: largest 5 values in staright table

Mon, 17 Oct 2016 09:18:54 GMT

Hai beck it resolved thank you so much u saved my time

Re: largest 5 values in staright table

Mon, 17 Oct 2016 09:22:55 GMT

Can u please help me out in this

Alternates states to compare highest 5 values in current month to the same values in previous month

Re: largest 5 values in staright table

Mon, 17 Oct 2016 18:43:47 GMT

Have you considered what should happen if the same regions have the same highest value?

Re: largest 5 values in staright table

Mon, 17 Oct 2016 18:47:06 GMT