Color the Bars when the range sum Sell-In Contribution up to 80% or 80% above

[Prinky]

Hello everyone,

I have a sales of fruit sample data. What I expect is the color bar when the ranges sum reaches 80% or 80% above. According to the screenshot below, it only colors the bars in green from Apple to Grapes, but the sum of Apple to Grapes is only RM 4,700, which has not yet reached 80% of the total sales of all fruits. The total sales of fruits is RM 6,000, which means that 80% of RM 6,000 is RM 4,800. Therefore, once the sales contribution reaches 80% or more, I want to color all those bars in green. Once the sum of the last row of the range reaches 80% or more, the following bars will not be displayed in green.

Based on the case below, the bar of Mangosteen should be also displayed in green color. This is because when add in Mangosteen, the total sales from Apple to Mangosteen is RM 4,850 which is up to 80% and 80% above.

Here is the color expression that I am currently using in the dashboard:

if((rangesum(above(Sum(Sales),0,rowno(total)))/sum(Total Sales))<= (80/100),green(),red())

Sample data from excel:

Data showing in qlik sense bar chart:

Please advise . Thanks.

[rubenmarin · Accepted solution]

Hi, to add that line I think only need to chek if the previous row was still lower than 80%, so:

if((rangesum(above(Sum(Sales),1,rowno(total)-1))/sum(Total Sales))<(80/100),green(),red())

Maybe you need to add an Alt([Expression],0) or an 'or rowno(total)=1' to keed the first bar green

[Prinky]

Hi  Rubenmarin,

First of all, thank you for your reply, and sorry for the late reply.

After I tried the expression you mentioned, the problem seems to be solved.

Thank you very much.