14 Replies Latest reply: May 19, 2010 3:41 AM by davissiew

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

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

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

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

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

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

• 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.
• 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?

• 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

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

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

All in all the thought process is to perform a cumulative total based on rank(Sum(Sales)) and then perform the IF > 0.50, RGB test to achieve the desired color banding. So the question, how to cumulate based on rank?

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

Sorry Amir, I gave you two fundamentally different solutions. If you could clarify which solution best describes your challenge it may help in aiding you further. Find short clarification below of the two methods mentioned so far:

The AGGR() solution is based on the assumption that you want to colour code your data based on sales % contribution of each result. I.e. with increased number of clients per category you are likely to never have any one client appearing in the 75%+ colour coding bucket as no single client is likely to contribute over 75% of the overall sales in a given category.

The second proposed solution using the PICK() function satisfies a different criteria all together: to divide your clients into sales quartiles - i.e. if there are 8 clients in 1 category, I would expect to find 2 clients in each quartile irrespective of the distribution of sales across the client population UNLESS two clients happen to contribute the same amount of sales, in which case it is ambiguous in which quartile the clients should end up in - you can manipulate where they end up by using the optional parameters in the rank() function.

Now - which method seems to be the closest to describing a solution to your requirement?

Regards,

Jonas

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

Hello Jonas,

Thanks so much for the detailed explanation of your methods. As clarified by you, they are both varying in their own degree however, the one closest to my need is neither. They both satisfy needs of a different order.

In my case, I am only looking to color band them based on a cumulative sales total as opposed to the individual sales result in your AGGR example. So for instance,if the first top 6 items in a category, (say footwear) contribute to a cumulative 50% of the total sales in that category then I'd like to have them banded one color. Then, if the next 2 items in the footwear category contributed to 65% of the total sales in that category, I'd like to have to banded another color.

I would like to have this banding done across all categories. What QV is doing for me is banding all categories' items based on the very first value of the category dimension. So if the first value was Footwear, then QV presumes all other categories' (like Toys, Clothing, Hardware etc.) items would also have the same banding as Footwear which is/may not be so.

However, if I select 1 category at a time, I am able to achieve the results for each category. That is a workaround for now but not the best as the user would have to click how many ever categories there may be to get their results. Hence, although I have a solution, it is not THE solution.

does that help explain my dilemma?

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

Hi Amir,

If I've understood you right, you would like to:

1. Cumulatively add up the sales for each customer in ascending order wrt the customer sales rank

2. Calculate the sales % based on the cumulative top ranked sales versus the overall sales in a category

3. colour code the output wrt 25% bandings

If that is correct, I think I've found a solution for you. Quite an interesting exercise which lead to finding a solution to a problem I have always thought was not solvable in QlikView: To present lower tier dimensions in a sort order that takes higher tier dimensional splits into account.

To solve the above:

1. Create a pivot chart with category as first dimension and a calculated dimension as your second dimension.

2. The calculated dimension defintion:
=aggr(dual(customer,Rank(Sum(sales))),category,customer)

Set sort order on 'Sort' tab to numeric ascending.

3. Create an expression of 'cumulative sales' as:
rangesum(above(sum(sales),0,rowno()))

4. Create an expression of 'total sales' as:
sum( aggr(sum(TOTAL <category> sales),category,customer))

I had to wrap calc in aggr() as the TOTAL qualifier did not cope with the calculated ranked dimension using standard TOTAL <category> syntax

5. Create an expression of 'ratio' as:
[cumulative sales] / [total sales]

6. Create the conditional format of the background colour as:
pick(ceil(ratio*4), RGB(0,255,255),RGB(150,255,255),RGB(200,255,255),RGB(255,255,255))

Voila!

Hopefully that did the trick this time.

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

Hi, anyone knows which formula to use if to sort this calculated dimension : =aggr(rangesum(above(sum(Sales),0,rowno())),Customer) ?? This will accumulate Sales figure but purely based on loading sort order. I need to accumulate this Sales figure based on highest Sales figure in descending order so that I can have real picture on the Top10 or Top20 or so. Appreciate if any expert could suggest, thx.