Bar Chart Sorting with limited values displayed

[PrzemekL]

Hello, 

I have a bar chart which is limited to display top 5 products based on their market share. I need it to also include a particular product, as one of the top 5 (even though it might be ranked as no. 10).

#Any ideas on how this could be achieved?

[Andrei_Cusnir · Accepted solution]

Hello,

If my understanding is correct you want to display top 5 products on a Bar Chart and then you also want to display 1 more product which is outside the top 5. To achieve that you can follow the steps below:

1. My dataset is:

2. I have created the Bar chart and instead of using the "Limitation" option, I have used the following expression as dimension:

=If(
  Value >= '$(=SubField(Concat(Aggr(Sum(Value), Product), '-', -Aggr(Sum(Value), Product)), '-', 5))', 
  Product, 
  If(
    Product = 'Product 1', 
    Product, 
    Null()
  )
)

First If statement checks if the Value is grater or equal to the value of the 5th product counting from top. If this statement is true the you show the product, otherwise we go to the 2nd If statement which checks, alternatively, if the Product equals to the name of the product outside the top 5 products. In the event that the 2nd condition is true, it will return as well the product or it will eventually return Null().  

To calculate the value of the 5th product from the top I have used the expression '$(=SubField(Concat(Aggr(Sum(Value), Product), '-', -Aggr(Sum(Value), Product)), '-', 5))'. Allow me to elaborate further on how this works:

1. Aggr() function will calculate the Sum of the values per product
2. Concat() will order the results in descending order and then will combine everything in a single string with values separated by '-'
3. SubField() will split the concatenated string based on '-' delimiter and then will take the 5th value. This will ensure that you always have the value of the 5th product so when checking for values grater than that, you will get the top 5 products.

3. The output is:

As you can see, we are displaying the top 5 products (6 to 10) and we also display the Product 1 which is not part of the top 5 products. The final column is the accumulation of returned Null() values for the products that we didn't want to present. You can remove the last column by unchecking the option "Include null values" on the dimension.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members. 
 

[PrzemekL]

Hello Andrei, 

Thank you very much for the response. I have tried the suggested solution and it is close to what I am after, but for some reason, it works slightly different than on your end (must be my fault, though). 

I have copied the formula you have kindly come up with and pasted it as a dimension for my bar chart. The only difference is that your 'Value' field is called 'Sales' and 'Product' is 'Corporation' in my data set - I have changed those to reflect my naming, screenshot below: 

And below is a screenshot of the chart that the formula generates. Effectively, I am seeing 2 bars, one with the 'Corporation' that I always want to display, regardless of it's position in the ranking and the grey bar reflecting 'Others', but unfortunately I am not seeing the top 5 competitors. 

Following your suggestion, I have also selected 'No Limitation' in the dimension's 'Limitation' drop-down list. 

[Andrei_Cusnir]

Hello,

I have a hypothesis that might help us narrow down the cause of the issue. On my side I have used the following expression:

'$(=SubField(Concat(Aggr(Sum(Value), Product), '-', -Aggr(Sum(Value), Product)), '-', 5))'

To elaborate in details, my graph shows the top 5 products based on the Sum of the Value field. On your side, you might be having a different graph with different data. For example you might be displaying the top 5 products based on Count of the value so you should use Count(Value) instead of Sum(Value) and the equivalent expression should be:

'$(=SubField(Concat(Aggr(Count(Value), Product), '-', -Aggr(Count(Value), Product)), '-', 5))'

What I am trying to say is that you should check the expression that you have in your Measure for the chart and then use that expression in the 2 points where I have "Sum(Value)". Try using:

'$(=SubField(Concat(Aggr(EXPRESSION, Product), '-', -Aggr(EXPRESSION, Product)), '-', 5))'

Where EXPRESSION = The expression that you used for your chart's measure.

I hope that this information was helpful!

[PrzemekL]

Hello Andrei, 

Unfortunately this still does not work 😞 

I have pasted my measure expression as 'Value', but still no luck. Below is the screenshot of the dimension formula: 

This is the measure formula:

And here is the chart it produces. 

[Andrei_Cusnir]

Hello,

Please keep in mind that this is just a workaround, so it means that most probably there would be use case scenarios were this might not be fully supported and your use case scenario could be one of those. I can see that you are using advance set analysis expression within the entire 'Value' expression, where you are trying to limit the calculation based on the max date value of the field [Month-Year] and only after that you are limiting the graph to the last 5 products.  

I would recommend to create a new table during reload, via Data load editor, where in this table you will have only the products' details that meet the criteria for [Month-Year] = Date(AddMonths(Max(Date), -1), 'MMM-YYYY'). After that you will only have to use "Sum(Sales)" instead of the entire advanced set analysis. 

As I have mentioned above, there is a chance that your use case scenario can't be covered by this workaround. You can try the recommendation stated above, however if it is not going to work, you can submit a feature request [1] to have additional options natively that will help you cover such use case scenarios.

I hope that this information is helpful! 

---

[1] https://community.qlik.com/t5/Knowledge/How-To-Submit-Feature-Requests-For-Qlik-Products/ta-p/171258...