topic Re: Top 10 In Pivot in QlikView

Top 10 In Pivot

Fri, 18 Jul 2014 06:52:15 GMT

Hi,

I know this has been asked and answered few times.I am having a pivot table with Dimensions as Customer Name and Month Name.

Expression as:

=(Count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={20}>}Distinct(Conditional_Field))+(vTEU)*(count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={40}>}DISTINCT(Conditional_Field))))

Now on the basis of these counts I want to show Top 10 customers.

Please help.

Thanks

Re: Top 10 In Pivot

jagan — Fri, 18 Jul 2014 07:06:34 GMT

Hi Ankit,

Try Rank() in dimension, check below expression as Calculated Dimension or use Customer as Dimension and use below as expression

=if(aggr(rank((Count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={20}>}Distinct(Conditional_Field))+(vTEU)*(count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={40}>}DISTINCT(Conditional_Field)))),4,0),P_ID) <= 10, CustomerDimensionName)

Note : Replace CustomerDimensionName with your actual dimension name.

Regards,

Jagan.

Re: Top 10 In Pivot

PradeepReddy — Fri, 18 Jul 2014 07:09:00 GMT

In Properties-->dimension Limits--> Restrict the data to Top 10

Re: Top 10 In Pivot

Fri, 18 Jul 2014 07:25:38 GMT

Hi Pradeep,

I am using a pivot table and i am not getting dimension Limits option.

Any other way?

Thanks

Re: Top 10 In Pivot

Fri, 18 Jul 2014 07:26:24 GMT

Hi Jagan,

I have tried this and unfortunately its not working.

Please suggest any other option.

Thanks

Re: Top 10 In Pivot

SergeyMak — Fri, 18 Jul 2014 07:28:21 GMT

Hi Ankit,

If Rank() doesn't work - Please share your app.

Re: Top 10 In Pivot

PradeepReddy — Fri, 18 Jul 2014 07:37:57 GMT

sorry it's my mistake while reading as Pie Chart...

Re: Top 10 In Pivot

Fri, 18 Jul 2014 08:15:03 GMT

Hi, PLease try this

(if(rank((Count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size<={20}>}Distinct(Conditional_Field))+(vTEU)*(count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={40}>}DISTINCT(Conditional_Field)))),4,0),P_ID) <= 10, CustomerDimensionName)

Re: Top 10 In Pivot

Fri, 18 Jul 2014 08:56:33 GMT

Hi,

I am using a pivot table with Dimensions as Customer Name and Month Name.

Expression as :

(Count({$<Year_Date={$(=max(ROAD_YEAR))},[Flag Type]={'Road'},Road_Size={20}>}Distinct(Conditional_Field))+(vTEU)*(count({$<Year_Date={$(=max(ROAD_YEAR))},[Flag Type]={'Road'},Road_Size={40}>}DISTINCT(Conditional_Field))))

Now can you help me with why we are using 'P_ID'.

Can you guide me with that.

Thanks

Re: Top 10 In Pivot

jagan — Fri, 18 Jul 2014 09:38:38 GMT

Hi Ankit,

Try this

=if(aggr(rank((Count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={20}>}Distinct(Conditional_Field))+

(vTEU)*(count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={40}>}DISTINCT(Conditional_Field)))),4,0),CustomerDimensionName) <= 10, CustomerDimensionName)

Note : Replace CustomerDimensionName with your actual dimension name.

Hope it helps you.

Regards,

Jagan.

Re: Top 10 In Pivot

antoniotiman — Fri, 18 Jul 2014 09:45:34 GMT

Hi,

Try (Calulated Dimension)

Aggr(If(Rank(Expression) <= 10,Customer),Customer)

REgards,

Antonio

Re: Top 10 In Pivot

Fri, 18 Jul 2014 10:02:47 GMT

Hi Jagan,

It was working, however now my expression is :

Can you guide me with this?

Thanks

Re: Top 10 In Pivot

jagan — Fri, 18 Jul 2014 10:06:16 GMT

Hi,

Try like this

Note : Replace CustomerDimensionName with your actual dimension name.

Hope it helps you.

Regards,

Jagan.

Re: Top 10 In Pivot

Fri, 18 Jul 2014 12:08:43 GMT

Hi Jagan,

Everything seems to be working now.One last help.Why its only showing me the Month of 'APR' while updating the data.

Thanks

Re: Top 10 In Pivot

jagan — Mon, 21 Jul 2014 01:32:50 GMT

Hi,

I didn't get you, is your data is there for all the months? Check data if it is there then try removing the Year_Date={$(=max(Rail_Year))} in set analysis and check.

Regards,

jagan.

Re: Top 10 In Pivot

Mon, 21 Jul 2014 06:20:52 GMT

Hi Jagan,

Yes my data is for all the months and if i remove Year_Date={$(=max(Rail_Year))} from my expression,no data is being displayed.

Thanks

Re: Top 10 In Pivot

jagan — Mon, 21 Jul 2014 06:31:29 GMT

HI,

Attach sample file so that it would be easier to fix it.

Regards,

Jagan.

Re: Re: Top 10 In Pivot

Mon, 21 Jul 2014 11:02:42 GMT

Hi Jagan,

PFA..and my expression will remain same that we discussed before.

Please help me to show Top10 Customer's

Thanks

Re: Re: Re: Top 10 In Pivot

SergeyMak — Tue, 22 Jul 2014 01:18:12 GMT

Hi Ankit,

PFA

I used expression

Sum({<[Customer Name] = {"=Rank(SUM(TEUS))<=10"}>}TEUS)