Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
Partner
Partner

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
Contributor

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

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).

Partner
Partner

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

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
Contributor

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

=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)
)))

Partner
Partner

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

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
Contributor

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

sorry Amir.

maybe Qv could do what you're thinking.

maybe if someone with more experience should advice you.

mov
Esteemed Contributor III

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

Next step to try is to add Category into the aggr functions.
Partner
Partner

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

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

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

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

Partner
Partner

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

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.