topic Re: Aggr In Pivot Table in App Development

Aggr In Pivot Table

Fiorrie — Fri, 27 Aug 2021 10:20:21 GMT

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.

Re: Aggr In Pivot Table

chrismarlow — Fri, 27 Aug 2021 17:11:00 GMT

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.

Re: Aggr In Pivot Table

Fiorrie — Mon, 30 Aug 2021 10:43:45 GMT

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.

Re: Aggr In Pivot Table

Fiorrie — Mon, 30 Aug 2021 13:54:57 GMT

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

Re: Aggr In Pivot Table

chrismarlow — Mon, 30 Aug 2021 19:04:28 GMT

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.

Re: Aggr In Pivot Table

Fiorrie — Tue, 31 Aug 2021 16:30:56 GMT

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

Re: Aggr In Pivot Table

chrismarlow — Tue, 31 Aug 2021 16:34:01 GMT

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.