15 Replies Latest reply: May 22, 2017 6:53 AM by Sunny Talwar

Create a Calculated Pareto Dimension for a Pie Chart

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

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

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

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

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

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

How can you have two fields with Type1 header?

• Re: Create a Calculated Pareto Dimension for a Pie Chart

So this would be the third pie?

• Re: Create a Calculated Pareto Dimension for a Pie Chart

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

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

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

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)/

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'}>} TOTAL Sales), 0,RowNo())) <= 0.8,

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)))),

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 = {'C'}, Type1 = {'M'}>}Sales))

• Re: Create a Calculated Pareto Dimension for a Pie Chart

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

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.