Question

[#1]

Is there a way I can show the top 5 claims and the top 5 incomes for each month in each year and the amount of those claims and income for my data set.

[MK_QSL]

Create a Pivot Table

Dimensions

Posting Year

Posting Month

%PolicyID

Expression

SUM({<Type = {'Claims'}, %PolicyID = {"=Rank(SUM({<Type = {'Claims'}>}Amount),4)<=5"}>}Amount)

[giakoum]

something like that?

[MK_QSL]

Looks like my above solution would not work..

Here is the new one..

Create a Pivot Table

Dimensions

Posting Year

Posting Month

Type

=IF(Aggr(Rank(SUM(Amount),4),[Posting Month],Type,[Posting Year],%PolicyID)<=5,%PolicyID)

The last one is Calculated Dimension

Expression

SUM({<Type = {'Claims'}>}Amount)

Same way you can find the Income also..

Hope this will help !

Dont forget to select SHow Partial Sum From Presentation Tab...

[#5]

Hi Stephen

Can you not try work it out yourself?

[#6]

Hi Manish,

I don't understand what you mean by calculated dimension.

Regards,

Stephen.

[#7]

Hi Ioannis,

Yes, like above but i do not know how to write the code so the top 5 is in a bar chart

[giakoum]

I reduced the data to 2011 2012 to make the file smaller

[#9]

Hi Manish,

Thanks for your help, this is amazing but far too detailed for what I am looking for. I am trying to show the top 5 claims in each year and represent it in a bar chart, I now don't think I need it per month. I also would like to show the lowest 5 claims in each year

Regards,

Stephen

[MK_QSL]

You mentioned in your question,,, each month and each year !!!!

Ok..so

Create a Bar Chart

Dimension

Posting Year

Type

=IF(Aggr(Rank(SUM(Amount),4),Type,[Posting Year],%PolicyID)<=5,%PolicyID)

Expression

SUM({<Type = {'Claims'}>}Amount)