Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
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?
Try my solution....
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
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.