topic Re: Create a calculated Pareto dimension involving Select in App Development

Create a calculated Pareto dimension involving Select

mrthomasshelby — Sat, 16 Nov 2024 09:44:11 GMT

Hello all, I have a part of the dataset as follows:

Vendor Sales Type Type1 Code

A 1300 C M 1234

B 2200 N S 1235

A 1400 N M 1236

B 2100 N M 1237

C 2500 C M 1240

A 300 N M 1239

C 900 C M 1240

D 3200 N M 1241

B 2000 C S 1242

D 1500 C S 1243

E 1100 N M 1246

F 3500 C M 1245

E 1500 C M 1246

G 1400 N M 1247

H 600 C M 1248

H 900 N S 1249

G 1200 C M 1247

Now if we perform a Pareto analysis on the above data, we see that the Vendors C,E,G and H are in Bottom 20%. Now I'd like to select the records of these Vendors with Type='N' and Type1='M'. So the dataset reduces to:

E 1100 N M 1246

G 1400 N M 1247

Now for these codes in the entire dataset, I'd like to display a stacked bar chart giving the amount Under the type 'C' and 'Top 80% N'(ie, Top 80% Vendors in Pareto among only Vendors in Type N). So the Type C here would display 2700(1500 from E and 1200 from G). The Top 80% N part would display 0 as neither E or G come under Top 80% in the Pareto done on only type 'N'.

If anyone can help me achieve this entire process(right from selecting only data from Bottom 20% of Type1='M' and Type = 'N' to the end) which seems very complicated and is just beyond me at the moment, I'd be really really grateful. TIA!

stalwar1 loveisfail sibusiso90

Re: Create a calculated Pareto dimension involving Select

sunny_talwar — Wed, 24 May 2017 14:37:45 GMT

Can you post a qvf where this data is already loaded.....

Re: Create a calculated Pareto dimension involving Select

mrthomasshelby — Wed, 24 May 2017 16:49:47 GMT

Hello. Here's the qvf. Hope you understood what I'm trying to achieve. To add to above, let's say there's an extra record:

Vendor Type Type1 Sales Code

A N M 800 1246

As A falls under Top 80% in Pareto among Vendors of type N, the bar chart should display 800 under 'Top 80%N'. Hope that makes it more clear. Thanks again!

Re: Create a calculated Pareto dimension involving Select

sibusiso90 — Wed, 24 May 2017 21:45:15 GMT

I hope this helps, but I didn't incorporate the 20% part please advice on what we should do because I didn't understand your statement there. I hope the QVF help but if you need more help don't hesitate to ask for help

Re: Create a calculated Pareto dimension involving Select

sibusiso90 — Wed, 24 May 2017 21:49:21 GMT

Please draw a chart or use simple numbers to drive all your calculations as to show how you get that vendor A is under 80%, not following the logic here at all which makes it hard to help

Re: Create a calculated Pareto dimension involving Select

sunny_talwar — Thu, 25 May 2017 01:10:15 GMT

Type A part can be accomplished with this

Sum(Aggr(If(Sum(Aggr(

Rangesum(

Above(

Sum({<Vendor, Type = {'N'}, Type1 = {'M'}>} Sales)/

Sum({<Vendor, Type = {'N'}, Type1 = {'M'}>} TOTAL Sales)

, 0, RowNo())

)

, (Vendor,(=Sum({<Vendor, Type = {'N'}, Type1 = {'M'}>} Sales),Desc)))) > 0.8

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

(Vendor,(=Sum({<Vendor, Type = {'N'}, Type1 = {'M'}>} Sales),Desc))))

Now I understood why Top 80% for Type N was 0 in the first dataset, but what I am not sure is why would adding this row of data change things?

Vendor Type Type1 Sales Code

A N M 800 1246

Does adding this bring this to the bottom 20% with Type='N' and Type1='M'? Because I thought we only need to bring those Vendors which 1st meet this criteria. Is that true for both Type C and Top 80% Type N?

Re: Create a calculated Pareto dimension involving Select

mrthomasshelby — Thu, 25 May 2017 05:17:58 GMT

My bad. I'm sorry for confusing you. What you understood initially was correct. Ignore that line please. I was trying to make something clear but gave a very poor example. Sorry for the trouble.

Re: Create a calculated Pareto dimension involving Select

mrthomasshelby — Thu, 25 May 2017 06:00:23 GMT

And yes we only need to get the Vendors which fall in the bottom 20% from that dataset for both Type C and Top 80% N.

Re: Create a calculated Pareto dimension involving Select

jonathandienst — Thu, 25 May 2017 06:31:25 GMT

If you had searched this site, you would have found

Recipe for a Pareto Analysis

Recipe for a Pareto Analysis – Revisited

And quite a few more...

Re: Create a calculated Pareto dimension involving Select

sunny_talwar — Thu, 25 May 2017 09:20:17 GMT

I believe this should work

Dimension

=ValueList('Type C', 'Top 80% N')

Expression

Pick(Match(ValueList('Type C', 'Top 80% N'), 'Type C', 'Top 80% N'),

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

Re: Create a calculated Pareto dimension involving Select

sunny_talwar — Thu, 25 May 2017 13:36:08 GMT

What rows constitutes of the 11,407.56?

Re: Create a calculated Pareto dimension involving Select

mrthomasshelby — Thu, 25 May 2017 13:37:46 GMT

Sales should be 11,407.56 under Type C. That is the final result.

Re: Create a calculated Pareto dimension involving Select

sunny_talwar — Thu, 25 May 2017 13:39:28 GMT

I get that, but which Vendors are you including in your total sales? Currently, C2, C3, D, & F were getting included... Are those not right?

Re: Create a calculated Pareto dimension involving Select

mrthomasshelby — Thu, 25 May 2017 13:43:07 GMT

Apparently not. So to make it clear, here's how I'd do it in excel:

1.Create a Pareto on the Overall Vendors. Select the fields which are of Type='N' and Type1='M' which fall under Bottom 20% Pareto.

2.Note the Codes of all these fields which are present after the above filters are applied.

3. Cross check only these codes against the Codes field in the entire data set.

4. See how much Sales of these fields fall under the Type 'C' or Top 80% N.

Re: Create a calculated Pareto dimension involving Select

mrthomasshelby — Thu, 25 May 2017 15:27:54 GMT

It's okay if you don't get the matching result too. Just giving me the expression would be enough. Thanks!

Re: Create a calculated Pareto dimension involving Select

mrthomasshelby — Fri, 26 May 2017 13:34:37 GMT

Hello. Here's the entire dataset. You can also find calculations done in the sheet. So to reiterate what I want.

1.Perform a Pareto analysis on the whole dataset Vendors.

2.Select the Vendors in the Bottom 20%. Within the transactions of these Bottom 20% Vendors, note the Codes which have Type='N' and Type1='M'.

3.Now cross check these noted codes from above against the entire dataset.

4.See how much of their values fall under the Type 'C' or Type 'Top 80% Pareto Vendors within Type N'.

The result for this dataset should be: Type 'C':$310,142.26 and Top 80% N:$ 106,552.52

Please help me out as I've been struggling with this one for a while now. TIA!

Re: Create a calculated Pareto dimension involving Select

sunny_talwar — Fri, 26 May 2017 15:27:25 GMT

Here you are

Dimension

=ValueList('Type C', 'Top 80% N')

Expression

Pick(Match(ValueList('Type C', 'Top 80% N'), 'Type C', 'Top 80% N'),

Sum({<Vendor, Type = {'C'},

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

Sum({<Vendor,

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

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

Type = {'N'}>} Sales))

Also, please find attached the application with this email.

Re: Create a calculated Pareto dimension involving Select

mrthomasshelby — Fri, 26 May 2017 15:28:36 GMT

Awesome! This is amazing stuff! I can't thank you enough!