Aggr In Pivot Table

[Fiorrie]

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.

[chrismarlow]

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.

[Fiorrie]

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.

[Fiorrie]

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

[chrismarlow · Accepted solution]

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.

[Fiorrie]

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

[chrismarlow]

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.