topic Re: top 10 values in QlikView

top 10 values

prma7799 — Fri, 10 Jul 2015 06:28:16 GMT

Hi all,

I have one fields called division it has 15 records now am using that as list box.

My requirement is i want only top 10 records from list box.

How can i achieve it.

Thanks

Re: top 10 values

jagan — Fri, 10 Jul 2015 06:36:53 GMT

Hi,

Try like this

=Aggr(If(FieldIndex('division', division) <= 10, division), division)

The best thing is arrive the top 10 fields in script and use that field in listbox

Data:

LOAD

If(RowNo() <= 10, division) AS Top10Div

FROM DataSource;

Now use Top10Div as listbox.

Hope this helps you.

Regards,

jagan.

Re: top 10 values

datanibbler — Fri, 10 Jul 2015 06:37:24 GMT

Hi,

you can add a new object and select the type >listbox<. Then you can add formulas (as well as fields) and you can also enter some condition. I haven't found out yet exactly how to do it, but experiment a bit with that (try with the max() function, that lets you specify the top_10).

Alternatively, you could use a straight_table chart for that, with a dimension of '1' (not to be displayed), so only one column will be visible.

HTH

Re: top 10 values

prma7799 — Fri, 10 Jul 2015 06:49:35 GMT

Hi Jagan,

=Aggr(If(FieldIndex('division', division) <= 10, division), division)

The above statement is not working.

Thanks

Re: top 10 values

Kushal_Chawda — Fri, 10 Jul 2015 06:50:25 GMT

what is logic to define Top 10 in list box? Is it on Sales or on some amount? If it is so you can do something like this

In listbox properties , in drop down select expression and use below expression

=aggr(if( rank(sum(Amount),4)<=10,Division), Division)

Please find the attached

Re: top 10 values

prma7799 — Fri, 10 Jul 2015 06:59:37 GMT

Hi Kush,

Can you explain me below statement

=aggr(if( rank(sum(division),4)<=10,Division), Division)

Thanks

Re: top 10 values

prma7799 — Fri, 10 Jul 2015 07:07:26 GMT

Hi Kush,

Very sorry I used your expression but value is not coming.

PFA in that i want red mark first top 10.

Re: top 10 values

Kushal_Chawda — Fri, 10 Jul 2015 07:09:08 GMT

Rank function is checking the Top 10 Values by Division using Rank(sum(Value),4)<=10, and assigning the Division name (if( rank(sum(Value),4)<=10,Division))by aggregating the values by division which is whole statement

Re: top 10 values

Kushal_Chawda — Fri, 10 Jul 2015 07:10:04 GMT

Please share your sample app

Re: top 10 values

prma7799 — Fri, 10 Jul 2015 07:14:46 GMT

PFA.

Re: top 10 values

Kushal_Chawda — Fri, 10 Jul 2015 07:24:02 GMT

There is no Value field to do the sum..

Re: top 10 values

prma7799 — Fri, 10 Jul 2015 07:38:15 GMT

Yes this is only dimension value I did sorting on below expression then i will get all top division .

SUM({$<FiscalYear={$(=max(FiscalYear))}>}[Gross Sales Amount])

Re: top 10 values

Kushal_Chawda — Fri, 10 Jul 2015 07:42:13 GMT

Try,

=aggr(if( rank(SUM({$<FiscalYear={$(=max(FiscalYear))}>}[Gross Sales Amount]),4)<=10,Division), Division)

Re: top 10 values

prma7799 — Fri, 10 Jul 2015 07:48:35 GMT

Good Job Friend.

It is working fine.

Can you add me in your Friend list.

Thanks

Re: top 10 values

datanibbler — Fri, 10 Jul 2015 07:49:05 GMT

Hello Kush,

I don't quite understand either. I know what aggr() does that IF() statement probably returns a NULL value if a Division is NOT among the top 10, so there's nothing to aggregate and it is not shown.

<=> What I don't understand is, where does that value 4 come from? From the brackets, it seems to be a parameter to rank() - but we are looking for the 10th highest value, not the 4th highest?

Can you please explain where that comes in?

Thanks a lot!

Best regards,

DataNibbler

P.S.: Ah, I see - please just correct me if I got it wrong - the rank() returns - well, the rank of a division rgd. the sum of their sales - the 4 is just the >modus< and tells QlikView what to do in case that two divisions have the exact same sum of Sales - then the first line in the table with this rank would get this rank, the next one would get the next rank and all subsequent ranks would be increased by 1. So this isn't absolutely necessary, but it can be helpful to avoid confusing results, depending on the data.

Re: top 10 values

Kushal_Chawda — Fri, 10 Jul 2015 10:50:34 GMT

Hi DataNibbler,

As we are aggregating the Values based on Division there will be a rare case where Value is not available for any of the Division.In this case you are correct ,it will not display anything.

But if value is available for Any one of the Division it will display the Division in listbox only if aggregate function is used. I have used 4 in Rank to display Lowest rank on first row, then incremented by one for each row.