sort the table based on other field

[arulsettu]

Hi

     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

[settu_periasamy]

Hi Arul,

Can you provide the sample data?

I think your can use the Dimension limit also..

[arulsettu]

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

[PradeepReddy]

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

[PradeepReddy]

can u share sample Application?

[tresesco]

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

[arulsettu]

i will upload the sample

[arulsettu]

please find the sample

[jagan · Accepted solution]

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.

[arulsettu]

Thank you very much jagan bro