topic Re: Question in QlikView

Question

Tue, 31 Mar 2015 15:51:08 GMT

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.

Re: Question

MK_QSL — Tue, 31 Mar 2015 16:04:33 GMT

Create a Pivot Table

Dimensions

Posting Year

Posting Month

%PolicyID

Expression

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

Re: Question

giakoum — Tue, 31 Mar 2015 16:10:55 GMT

something like that?

Re: Question

MK_QSL — Tue, 31 Mar 2015 16:17:05 GMT

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...

Re: Question

Tue, 31 Mar 2015 16:21:38 GMT

Hi Stephen

Can you not try work it out yourself?

Re: Question

Tue, 31 Mar 2015 16:23:09 GMT

Hi Manish,

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

Regards,

Stephen.

Re: Question

Tue, 31 Mar 2015 16:28:03 GMT

Hi Ioannis,

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

Re: Question

giakoum — Tue, 31 Mar 2015 16:33:22 GMT

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

Re: Question

Tue, 31 Mar 2015 16:35:16 GMT

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

Re: Question

MK_QSL — Tue, 31 Mar 2015 16:37:18 GMT

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)

Re: Question

Tue, 31 Mar 2015 16:38:44 GMT

Hi Manish,

I also do not need the policy id. I just want to show the top 5 claims in each year in a bar chart.

Thanks,

Stephen.

Re: Question

buzzy996 — Tue, 31 Mar 2015 16:39:10 GMT

look at last sheet and let me know if not right1

Re: Question

MK_QSL — Tue, 31 Mar 2015 16:41:16 GMT

Dear Stephen,

You can change the %PolicyID to whatever field you want...!

i.e. ClaimID or ClaimNumber

Re: Question

Tue, 31 Mar 2015 16:49:38 GMT

Hi Manish,

Sorry about being unclear. I do not know how to do the type =if(Aggr etc.)

Would you mind showing me how I do this. Do I add type to dimensions and then click type and click calculated dimension and add the if statement

Regards,

Stephen.

Re: Question

MK_QSL — Tue, 31 Mar 2015 16:57:11 GMT

What is the Field (Claim) you want to show?

Re: Question

Tue, 31 Mar 2015 16:58:07 GMT

Hi Shiva,

Thanks for your help, it is not quite what I am trying to show, I am looking to show the top 5 claim amount in each year, so say in 2012 the top 5 claim amount are 25,000 and 20,000 and 18,000 and 15,000 and 10,000 I just want to show this in a bar chart. I probably did not explain myself correctly

Regards,

Stephen.

Re: Question

buzzy996 — Tue, 31 Mar 2015 17:01:45 GMT

just remove months field from dimension tab and set largets as 5 in dimesnions limits tab.

let me know,i'll share app tomorrow.

Re: Question

Tue, 31 Mar 2015 17:03:44 GMT

Manish,

There is no field called claims, type is made up of income and claims. What I want to show is the 5 top amount for claims for each year, so say the top 5 claims are 50,000 and 40,000 and 25,000 and 20,000 and 10,000, i want to show this in a bar chart. I am just trying to show the highest 5 claims in the year

Regards,

Stephen.

Re: Question

MK_QSL — Tue, 31 Mar 2015 17:08:09 GMT

But Top 5 Amount should be based on any field... ! If you do Year and SUM(Amount) you will get only 1 line per Year...

So if it is %Invoice ID...use attached...

Re: Question

buzzy996 — Wed, 01 Apr 2015 09:02:14 GMT

pls check the attached sheet4 and let me know,

here wht i'm trying to show

1.TOP 5 Claims By Year & Month, just we are showing only top 5 claims months by year,not all the months in the same year.

2.TOP 5 Claims By Year & %IncidentID : Top 5 claims by by year and IncidentID(we need one field like this,but u can cahnge to any field) by year.