Total of the top x items and total of the total in straight (or pivot) table

[jjordaan]

Hi all,

A customer wanted to have an additional table in his QlikView application.

The customer wants to see the top 10, 20, 30 etc (depends on the value of the variable) items.

Besides that the customer wants to see the total of the top 10, 20, 30 etc items but he also wants to see the total of the total (all the items)

In the beginning I thought that is not so difficult.

We can use dimension limits. But with this feature we cannot show the total of the top 10, 20, 30 etc items. We can show the total of the total.

Then I tried to use the Max number setting in the presentation tab. With this I only see the total of the total and not the total  of the  top 10, 20, 30 etc items

So I thought lets use the aggr function in a calculated dimension. But with this solution we can only show the total of top 10, 20, 30 etc items and not the total of the total.

Am i missing something?

Has anyone ran into this requirement and found a solution for it?

Thanks in advance

[Anonymous]

did you check out the whats new in Qlikview 11.qvw example? i think the products tab in there does pretty much what your requirement is and should be a great start for you.

[mdmukramali]

Dear ,

i think your looking for to see in total business who are our TOP 10 or 20 ,30 etc customers and their details.

In What is new in Qlikview 11 Demo application you can find one sample scenario in Products Tab.

find the attached same.

i hope it will help you.

Thanks,

Mukam

[MK_QSL]

Consider below data

Sales:

Load * Inline

[

  Customer, Sales

  A, 100

  B, 200

  C, 150

  D, 50

  E, 250

  F, 600

  G, 100

  H, 120

  I, 400

  J, 125

  K, 230

  L, 220

];

Now create a variable vTOP and assign value 3

Create a Straight Table

Dimension

Customer

Expression

SUM({<Customer = {"=Rank(SUM(Sales),4)<=$(vTOP)"}>}Sales)

This will give you Customer with Sales who are in top 3

If you want TotalSales of Top 3 customer, use below in your text box

=SUM(Aggr(If(Rank(SUM(Sales),4)<=$(vTOP),SUM(Sales)),Customer))

If you want total sales, use

SUM(Sales)

or

SUM(TOTAL Sales)

or

SUM({1}Sales)

or

SUM(All Sales)

Change value of vTOP to 30, 50 or 10 as per you requirements

[jjordaan]

Hi Mohammed and jsaradhi,

Thank you for your replies.

I ques I wasn't using dimension limits in a correct way.

I know that within large applications dimension limits can decrease the performance.

Do you know an alternative way?

And do you know how I can use this requirement within a pivot table?

[MK_QSL]

Try my solution....

[jjordaan]

Hi Manish,

This is the end result I'm looking for

Only this created with a straight table and dimension limits like jsaradhi and Mohammed advised me to.
I'm hoping I can have the same result within a pivot table.

I don't think I can created it with your solution?

Or am I wrong?

Thanks for your help

[david_pearson]

similar problem in QS,

i set the table to a fixed number, top top based on CAUSEDESCRIPTION as the dimension. i then wanted to count the number of CLAIMNO.

in your expression you need something like 

IF(Aggr(Rank(SUM(CLAIMNO),4),CAUSEDESCRIPTION)<=10,CAUSEDESCRIPTION)

then in your measures Count(CLAIMNO).

then only problem im currently trying to sort out is to get a % of the total as it is still reverting to the full table total rather than the top 10 total.