6 Replies Latest reply: Dec 24, 2014 5:59 AM by Jeroen Jordaan

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

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?

• ###### Re: Total of the top x items and total of the total in straight (or pivot) table

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.

• ###### Re: Total of the top x items and total of the total in straight (or pivot) table

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.

Thanks,

Mukam

• ###### Re: Total of the top x items and total of the total in straight (or pivot) table

Consider below data

Sales:

[

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

• ###### Re: Total of the top x items and total of the total in straight (or pivot) table

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?

• ###### Re: Total of the top x items and total of the total in straight (or pivot) table

Try my solution....

• ###### Re: Total of the top x items and total of the total in straight (or pivot) table

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?