Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
avastani
Partner - Creator III
Partner - Creator III

Pivot Table - Color Banding a cell based on Top 25, Top 50 in Sales

How can I format the background color of a cell based on the test, top categories in sales making 25% of total sales should be green and the next 25% making total 25-50% of sales yellow and the remainder red?

i can get the sales to have a rank which gives me the top categories in sales and another column giving their sales% of total sales, but now I want to color their background.

any help is most appreciated, thanks guys

14 Replies
mongolu
Creator
Creator

In both Dimensions (D) and Expressions (E) (chart properties), you have a plus sign in front of the name (D,E).
If you expand it, you can see some properties regarding that D or E. Like Background Color, Text Color, etc.
In there, you could do the test (as a result for the color you could use the function RGB).
Something like "=if(rank=1,rgb(0,0,0),if(rank=2,rgb(100,0,0),...".

Although this should take you where you want, i think it'll slow down the chart calculations (one more to go).

avastani
Partner - Creator III
Partner - Creator III
Author

While I knew that is where the change needs to be made, the problem I am having is with the expression. The question was geared toward what the expression would look like for color banding based on Rank and Cumulative Sales % Total = 25%, 50% ... etc. Any direction or tip is most appreciated.

mongolu
Creator
Creator

=IF(AGGR(SUM(SALES)/SUM(TOTAL SALES),CLIENT)>0.75,RGB(255,0,0),
IF(AGGR(SUM(SALES)/SUM(TOTAL SALES),CLIENT)>0.50,RGB(255,100,100),
IF(AGGR(SUM(SALES)/SUM(TOTAL SALES),CLIENT)>0.25,RGB(255,200,200),RGB(255,255,255)
)))

avastani
Partner - Creator III
Partner - Creator III
Author

that is what I had as well, but it does not work because it does not take individual rankings by Category into consideration. I thought QV might be smart to pick that up but guess not. below is the result I get. If you see Shoes below, the banding goes from rank 5 to rank 7 skipping rank 6 item for banding.

However, if I pick a category I get the desired result. Just not on a whole view basis which would have been quite nice and expected of QV.

mongolu
Creator
Creator

sorry Amir.

maybe Qv could do what you're thinking.

maybe if someone with more experience should advice you.

Anonymous
Not applicable

Next step to try is to add Category into the aggr functions.
avastani
Partner - Creator III
Partner - Creator III
Author

Tried Michael's suggestion as well but to no success. The problem again is that QV does not recurse the banding expression but simply uses the first dimension's value and then presumes all other dimension values are based on the banding of the first dimension's value it used to obtain the banding.

would that be considered a bug or working as designed?

Not applicable

Hi Amir,

This is achievable, we just need to use the rich syntax in which we can formulate expressions. Making the suggested formula work could look like:

=IF(AGGR(SUM(SALES)/SUM(TOTAL <CATEGORY> SALES),CATEGORY,CLIENT)>0.75,RGB(255,0,0),
IF(AGGR(SUM(SALES)/SUM(TOTAL <CATEGORY> SALES),CATEGORY,CLIENT)>0.50,RGB(255,100,100),
IF(AGGR(SUM(SALES)/SUM(TOTAL <CATEGORY> SALES),CATEGORY,CLIENT)>0.25,RGB(255,200,200),RGB(255,255,255)
)))

or an alternate more optimised solution using a single aggr statement could look like:

=pick(ceil(aggr(rank(sum(sales))/count(TOTAL <category> client),category,cilent) * 4) , RGB(255,0,0),RGB(255,100,100),RGB(255,200,200),RGB(255,255,255))

Hope it helps and happy Qliking!

Cheers,

Jonas

avastani
Partner - Creator III
Partner - Creator III
Author

Jonas,

Thanks for the tip, and yes, this does give direction in the way of a working solution with the PICK() function but it does not achieve proper results whereby in some instances it bands data beyond 50% improperly to the first RGB value. The AGGR function however, does not achieve any results but I will continue my search in this direction.

As a twist to the above, the PICK function though handy is not viable when the user wants to band the results at uneven periodic intervale i.e. Top 30%, Top 55%, Top 70%. But given the direction above, there must be a solution in there somewhere.