Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jjordaan
Partner
Partner

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?

Thanks in advance

7 Replies
jsaradhi
Specialist
Specialist


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
Specialist III
Specialist III

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

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
Partner
Partner
Author

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

Try my solution....

jjordaan
Partner
Partner
Author

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
Contributor III
Contributor III

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.