topic Re: sort the table based on other field in QlikView

sort the table based on other field

arulsettu — Fri, 19 Feb 2016 04:04:31 GMT

i am trying to achieve in straight table some thing like this

dimension is drill down

make

model

use

expression is finding ratio

sum(commission)/sum(amount)

now what i need is take the top ten ratio based on vehicle count. that means highest vehicle count of ratio values should come.

for this should i need to use rank function?

can any one suggest some idea

thank you

Re: sort the table based on other field

settu_periasamy — Fri, 19 Feb 2016 04:40:44 GMT

Hi Arul,

Can you provide the sample data?

I think your can use the Dimension limit also..

Re: sort the table based on other field

arulsettu — Fri, 19 Feb 2016 04:49:56 GMT

that vehicle count is not in the dimension. it was different field that is the issue.

Re: sort the table based on other field

PradeepReddy — Fri, 19 Feb 2016 04:52:46 GMT

you can use either Rank() or Dimension Limits...

Re: sort the table based on other field

PradeepReddy — Fri, 19 Feb 2016 04:53:29 GMT

can u share sample Application?

Re: sort the table based on other field

tresB — Fri, 19 Feb 2016 04:55:04 GMT

Yes, may be using rank() in combination with aggr() or set analysis you get that. A sample qvw would be much easier to help.

Re: sort the table based on other field

arulsettu — Fri, 19 Feb 2016 05:19:53 GMT

i will upload the sample

Re: sort the table based on other field

arulsettu — Fri, 19 Feb 2016 05:29:08 GMT

please find the sample

Re: sort the table based on other field

jagan — Fri, 19 Feb 2016 05:38:47 GMT

Hi,

Try this expression

For Count:

If(Rank(count({<BW_UW_YEAR={$(=max(BW_UW_YEAR))}>}BW_VEH_ID)) <= 10, count({<BW_UW_YEAR={$(=max(BW_UW_YEAR))}>}BW_VEH_ID))

For Ratio:

If(Rank(count({<BW_UW_YEAR={$(=max(BW_UW_YEAR))}>}BW_VEH_ID)) <= 10,

sum({<BW_UW_YEAR={$(=Max(BW_UW_YEAR))}>}BW_CLM_INC) /sum({<BW_UW_YEAR={$(=Max(BW_UW_YEAR))}>}BW_NET_PREM))

Regards,

Jagan.

Re: sort the table based on other field

arulsettu — Fri, 19 Feb 2016 05:42:28 GMT

Thank you very much jagan bro

Re: sort the table based on other field

settu_periasamy — Fri, 19 Feb 2016 05:49:24 GMT

Hi,

This will also give the same result..

Dimension Limit -> First 11

Re: sort the table based on other field

arulsettu — Fri, 19 Feb 2016 05:51:31 GMT

Thanks setu Count column i am not going to show its for reference

Re: sort the table based on other field

PradeepReddy — Fri, 19 Feb 2016 06:00:40 GMT

you can hide the 'Count' expression in presentation tab... then the result would be what you are expecting..

Re: sort the table based on other field

settu_periasamy — Fri, 19 Feb 2016 06:06:05 GMT

Hi Arul,

Actually, it is not based on your Count Expression, It is based on the Largest 11 value..

if you remove the 'Count' Expression, it won't affect here...

Check that..

Re: sort the table based on other field

arulsettu — Fri, 19 Feb 2016 06:24:55 GMT

No its not working. it bring only largest 10 loss ratio

Re: sort the table based on other field

settu_periasamy — Fri, 19 Feb 2016 06:37:38 GMT

Hi Arul,

It is working for me.

I think, you tried with formula and Dimension Limit..

Check the Attachment..