Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Custom sorting bar chart to include "Other" dimension value.

Given a table with the following schema:

DateProductUnits Sold
1/1/2014Hat20
1/1/2014Sweater30
1/2/2014Hat10
1/2/2014Scarf5

I have a bar chart with the following requirements: 

Display the Units Sold for the top 6 Products by Date for a given period.

Include an "Other" product to represent values not in the top 6.

Sort the bar segments according to the rank of Units Sold on the last Date of the given period such that the Product with the most sales over the period is assigned to the first (bottom) bar segment, the next highest product gets the next highest bar segment, etc.

So, I create the chart with two dimensions (Date and Product) and an expression of sum([Units Sold]).

I set a Dimensional Limit of Top 6 on Product, and enable "Other".

In the Sort expression I copy the chart expression and add set analysis resulting in sum({<Date={$(=max(Date))}>}[Units Sold])

This works exactly as I want with the exception that the chart segment assigned to "Other" isn't affected by the Sort expression and always ends up being stuck on the top of each bar.

So, is there a way to modify my Sort expression so that it affects the "Other" segment or do I have to disable the built-in "Other" value and calculate an "Other" value myself and then include my "Other" calculation in my Sort expression? If the solution is to calculate "Other" myself, I'd appreciate any pointers on how to do that given that I've still got the Top 6 dimensional limit in place.

Thanks.

Steven

1 Solution

Accepted Solutions
maxgro
MVP
MVP

perhaps with rank and aggr you can sort "Other"

see attachment

calc dim is

=if(aggr(rank(sum(Units)), Product)<=3,Product, 'Other')

sort order is (d is date)

=sum({$<d={'$(=date(max(d)))'}> }Units)

View solution in original post

3 Replies
MK_QSL
MVP
MVP

Can you provide some more sample data?

maxgro
MVP
MVP

perhaps with rank and aggr you can sort "Other"

see attachment

calc dim is

=if(aggr(rank(sum(Units)), Product)<=3,Product, 'Other')

sort order is (d is date)

=sum({$<d={'$(=date(max(d)))'}> }Units)

View solution in original post

Not applicable
Author

Similar requirement, explained in this blog post: Qlikview Bitz and Pieces: Custom Sort in Qlikview