Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Need to show top 20 locations by sales

Hi All,

I need to show top 20 locations by sales, however not able to achieve the same in straight table/pivot table using dimension limits or aggregate function.

PFA the application file.

If a location is lying in top-20 group, it is displayed for other institutes as well.

The required output should look like following but not getting this.

   

Institution LocationTotal Sales
TCSSTower Hall12,23,390.
MSUAlumni Tower 6,81,855.
CCBerry Hall6,55,460.
MSUThird Street Eats5,89,740.
UCFMove-In Weekend5,66,850.
TUSFFCO 3rd Floor3,83,936.
TUSC3,36,957.
UCFSU2,97,715.
CUAcademic Circle2,77,680.
WCUPLawrence2,23,565.
UVFFC1,95,006.
WCUCourtyard FC1,90,008.
BUP1st Floor Commons1,79,479.
USMThad Cochran1,76,030.
WCUUC FC1,50,041.
GVSUConnection1,49,125.
MSUAcademic Insight1,48,180.
GVSUFFC1,23,450.
SLUSU50,805.

Thanks.

Reema

1 Solution

Accepted Solutions

Try with this expression

If(Rank(TOTAL Sum([Total Sales])) <= 20, Sum([Total Sales]))


Capture.PNG

View solution in original post

6 Replies
Creator III
Creator III

You could use a straight table and dimension limits tab for the top 20 Sales

Father/Husband/BI Developer

Try with this expression

If(Rank(TOTAL Sum([Total Sales])) <= 20, Sum([Total Sales]))


Capture.PNG

View solution in original post

Partner
Partner

Hope your looking for the same, find attached file.

Contributor
Contributor

Thanks, this is working however pivot table is not getting sorted as straight table is sorted in descending order.

Even after giving expression for sorting.

Is it feasible to sort the pivot table too?

You can look here for help with sorting in pivot table

Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level)

Contributor III
Contributor III

Hi Reema,

You could use a straight table and dimension limits tab for the top 20 Sales.