Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone
I hope someone can help. I am using Pareto Analysis to evaluate how many customers are doing 10/20/30...% of sales. My pivot table looks like below.
Expression that calculates Cumulative Revenue:
RangeSum(Above(Sum(sales), 0, RowNo()))
Expression that calculates %Cumulative:
RangeSum(Above(Sum(sales), 0, RowNo())) / Sum(total sales)
Expression that calculates Customer Number:
If(RangeSum(Above(Sum(sales), 0, RowNo())) / Sum(total sales) <= 0.1, Count(distinct customer)
So everything is working, except I need to see these 10/20/30...% of sales in customer per market (below table called outcome). Once I switch market with customer order, it evaluates everything per market not per customer logically.
I need to sum up those 10% sales by customer number and to see them split by market (with aggr fuction probably)
Any help much appreciated.
Hi,
I think I can see where you want to go, although still have some doubts on parts of what you are doing.
So using the rangesum/total sales I think is giving you the largest customers until you have hit a total of 10% of sales, this fits in with the pareto style formulae, but which might not quite be what might be understood as 'top 10% of sales' as an alternative it could be taken to mean the 10% of customers with largest sales, irrespective how much you expect that to be (not sure I would call that a pareto analysis).
Either way you are looking to then count by customers that are in that set across a second dimension. I've fabricated some data that (sort of) fits an 80/20 pareto - so my customers F & G are the 2 largest & sum to 80% (if I had looked at 10%, either way I would have needed more customers ...). F has sales in 3 markets, G in 1, which is one of F's, so I expect to see Z have 2 (F & G) and X & Y 1 (F).
So top right then shows this following the pareto recipe using a calculated dimension to class as ABC, bottom right then amends the formula a bit to return null if not in the A class & then not show nulls, which is close to what I think you want.
The presentation leaves a bit to be desired (can't seem to be able to hide the column and it still work), but the actual values seem OK. I've not tested this a lot, so that might be a fluke, but as I am not sure it is quite where you want to go easier to share first.
Cheers,
Chris.
Hi,
Struggling a bit to follow your numbers. So Revenue=Sum(Sales) & Cumulative Revenue your formula works. I assume there are some other records that are not part of the set shown that mean these 7 form the top 10%, but not then sure how your %Cumulative can work given F & G both show 10% & even why you would choose to order A-G as you have, is G not my first contributor to the 10% being the biggest?
Have you seen the following blog post?
Recipe for a Pareto Analysis – Revisited - Qlik Community - 1473684
Cheers,
Chris.
hi Chris
Yes I saw the article. Yes Revenue = Sales. That is just an example, the order A-G is not order, its hypotetical customer. I only work with what I presented above. The last F-G was e.g. 10.1 and 10.5 but you are right the order should have be opposite coming from G to A
None of your questions are a problem here, but rather to make this to be functionable when I switch Customer ID with Market in a pivot table.
Maybe to add, to help you understand more, I have following expression:
If(RangeSum(Above(sales), 0, RowNo())) / Sum(total sales) <= 0.1, (customer))
I want to incorporate this below statement (or through aggr function), to show the result I calculated above (how many customer per market were in top 10% of sales)
Count(distinct TOTAL <market> customer)
Basically to combine these two and make it function, which i was not successful so far.
Thanks for your thoughts
Hi,
I think I can see where you want to go, although still have some doubts on parts of what you are doing.
So using the rangesum/total sales I think is giving you the largest customers until you have hit a total of 10% of sales, this fits in with the pareto style formulae, but which might not quite be what might be understood as 'top 10% of sales' as an alternative it could be taken to mean the 10% of customers with largest sales, irrespective how much you expect that to be (not sure I would call that a pareto analysis).
Either way you are looking to then count by customers that are in that set across a second dimension. I've fabricated some data that (sort of) fits an 80/20 pareto - so my customers F & G are the 2 largest & sum to 80% (if I had looked at 10%, either way I would have needed more customers ...). F has sales in 3 markets, G in 1, which is one of F's, so I expect to see Z have 2 (F & G) and X & Y 1 (F).
So top right then shows this following the pareto recipe using a calculated dimension to class as ABC, bottom right then amends the formula a bit to return null if not in the A class & then not show nulls, which is close to what I think you want.
The presentation leaves a bit to be desired (can't seem to be able to hide the column and it still work), but the actual values seem OK. I've not tested this a lot, so that might be a fluke, but as I am not sure it is quite where you want to go easier to share first.
Cheers,
Chris.
Hi Chris
I reviewed your solution one more time, and I think I finally got to some conclusion. It is not working 100% now, but I will manage.
Many thanks for your time appreciated!
Have a good day
Jana
Hi,
Glad it helped. The pareto recipe used {1} to ignore selections in parts of it ... you may want to strip that out.
Cheers,
Chris.