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
avastani
Partner - Creator III
Partner - Creator III
Author

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?

Not applicable

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

avastani
Partner - Creator III
Partner - Creator III
Author

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?

Not applicable

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.

Not applicable

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.