topic Re: Create a Calculated Pareto Dimension for a Pie Chart in App Development

Create a Calculated Pareto Dimension for a Pie Chart

mrthomasshelby — Sun, 21 May 2017 17:53:12 GMT

Hello all! I have a dataset that is as follows:

Vendor Sales Type

A 1300 C

B 2200 N

A 1400 N

B 2100 N

C 2500 C

A 300 N

C 900 C

D 3200 N

B 2000 C

D 1500 C

E 1100 N

F 3500 C

E 1500 C

G 1400 N

H 600 C

H 900 N

G 1200 C

Now I would like to create a Calculated dimension to use in a Pie chart. The Pie chart should have the Dimensions as 'C' and 'N Top 80%'. To further break it down, I'd like to show the % of Sales coming under the Type C and % of Sales coming under the Top 80%(Pareto) Vendors in Type N. Can you please help me create a calculated dimension to achieve this? TIA!

Re: Create a Calculated Pareto Dimension for a Pie Chart

sunny_talwar — Sun, 21 May 2017 18:01:05 GMT

Can you put down the result you expect to see from this data in a tabular form for us to understand your requirement?

Re: Create a Calculated Pareto Dimension for a Pie Chart

mrthomasshelby — Sun, 21 May 2017 18:17:47 GMT

Sure. Now what I'd like for the Pie chart to have is two partitions: Sales under Type 'C' and Sales coming from Top 80% Vendors in Type N. If we perform a Pareto analysis on the above dataset within Type N, we can see that the Vendors 'A','B' and 'D' fall into the Top 80% category. So the result should display as follows: 9200 Under Top 80% Type N

Vendor Split: A:1700(1400+300),

B:4300(2100+2200),

D:3200) and

15000 under Type C:

(Vendor Split:A:1300,

B:2000,

C:3400(2500+900),

D:1500,

E:1500,

F:3500,

G:1200,

H:600).

Re: Create a Calculated Pareto Dimension for a Pie Chart

sunny_talwar — Sun, 21 May 2017 18:28:22 GMT

Something like this?

Dimension

=ValueList(1, 2)

Expression

Pick(ValueList(1, 2),

Sum(Aggr(If(Rangesum(Above(Sum({<Vendor, Type = {'N'}>} Sales)/Sum({<Vendor, Type = {'N'}>} TOTAL Sales),0,RowNo()))<=0.8, Sum({<Type = {'N'}>}Sales)), (Vendor,(=Sum({<Vendor, Type = {'N'}>} Sales),Desc)))),

Sum({<Type = {'C'}>}Sales))

Re: Create a Calculated Pareto Dimension for a Pie Chart

mrthomasshelby — Sun, 21 May 2017 18:36:20 GMT

As usual you are correct! Could you please explain a little bit about the expression and the ValueList function or give the relevant links? Much much appreciated! Thanks a lot!

Re: Create a Calculated Pareto Dimension for a Pie Chart

sunny_talwar — Sun, 21 May 2017 18:38:34 GMT

Valuelist is a synthetic dimension. I can go over its explanation, but some of the other guys have already done the great work. Check these links out

ValueList() – For those tricky situations

Missing Manual - ValueLoop() & ValueList()

ValueList - chart function ‒ Qlik Sense

Re: Create a Calculated Pareto Dimension for a Pie Chart

mrthomasshelby — Sun, 21 May 2017 18:58:12 GMT

Sorry for the trouble but let's say there's another field for the data as follows:

Vendor Sales Type Type1

A 1300 C M

B 2200 N S

A 1400 N M

B 2100 N M

C 2500 C S

A 300 N M

C 900 C M

D 3200 N M

B 2000 C S

D 1500 C S

E 1100 N S

F 3500 C M

E 1500 C M

G 1400 N S

H 600 C M

H 900 N S

G 1200 C M

Now I'd like to display the same pie chart above for the Sales of in Bottom 20% Pareto of Type1='M'. How should I modify the above expression?

Re: Create a Calculated Pareto Dimension for a Pie Chart

sunny_talwar — Sun, 21 May 2017 19:01:03 GMT

How can you have two fields with Type1 header?

Re: Create a Calculated Pareto Dimension for a Pie Chart

mrthomasshelby — Sun, 21 May 2017 19:02:42 GMT

My bad! I've made the correction.Thanks!

Re: Create a Calculated Pareto Dimension for a Pie Chart

sunny_talwar — Sun, 21 May 2017 20:39:30 GMT

So this would be the third pie?

Re: Create a Calculated Pareto Dimension for a Pie Chart

mrthomasshelby — Mon, 22 May 2017 04:04:57 GMT

No. I would like to display the same pie chart but this time only for the data which comes under Bottom 20% Pareto in Type1='M'. Thanks!

Re: Create a Calculated Pareto Dimension for a Pie Chart

sunny_talwar — Mon, 22 May 2017 09:09:23 GMT

Would you be able to provide the expected output just like you did the last time.

Re: Create a Calculated Pareto Dimension for a Pie Chart

mrthomasshelby — Mon, 22 May 2017 09:25:19 GMT

1.I'd like to perform a Pareto analysis on the above dataset with Type1='M'. So the data set reduces to:

Vendor Sales Type Type1

A 1300 C M

A 1400 N M

B 2100 N M

A 300 N M

C 900 C M

D 3200 N M

F 3500 C M

E 1500 C M

H 600 C M

G 1200 C M

If you perform Pareto analysis on this data set, you'll see that Vendors C,E,G and H fall under the Bottom 20% Category. So, our data set now will be:

C 900 C M

E 1500 C M

H 600 C M

G 1200 C M

Now on this data set I'd like to show the above mentioned pie chart with split according to 'C' or 'Top 80% N' In this case, it should display 100% Under 'C'.

Re: Create a Calculated Pareto Dimension for a Pie Chart

sunny_talwar — Mon, 22 May 2017 09:42:35 GMT

Something like this

Pick(ValueList(1, 2),

Sum(Aggr(If(Rangesum(Above(

Sum({<Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'N'}, Type1 = {'M'}>} Sales)/

(Vendor,(=Sum({<Vendor= {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'N'}, Type1 = {'M'}>} Sales),Desc)))),

Re: Create a Calculated Pareto Dimension for a Pie Chart

mrthomasshelby — Mon, 22 May 2017 09:46:05 GMT

Whoa! That looks so complicated! But it works I guess! Thanks a lot again! It's going to take a while for me to figure out how this expression works I guess! Thanks a lot Sunny! I have learnt something new and really useful again!

Re: Create a Calculated Pareto Dimension for a Pie Chart

sunny_talwar — Mon, 22 May 2017 10:53:48 GMT

I guess breaking it down might help...where I have repeated this set analysis multiple times

{<Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'C'}, Type1 = {'M'}>}

and the important part was that you only wanted vendors which were bottom 20% in Type1, which is this part

Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}

To see how this behave, create a table with Vendor as dimension and this expression

=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc)))

You will only see C,E,H,G have value greater than 0.

Re: Create a Calculated Pareto Dimension for a Pie Chart

sreelathamohan — Thu, 05 Mar 2020 17:16:10 GMT

Hello Sunny,

I have a similar requirement just that mine is a bar chart and I want to show all the bars with their respective dimension values that contribute to top 80% and aggregate 81%-100% as "Others" for the last dimension bar. Is this possible? Any help would be greatly appreciated. Please see attached for an example of how my bar chart should look like.

Thanks.