
Re: Create a calculated Pareto dimension involving Select
Sunny Talwar May 24, 2017 10:37 AM (in response to Phalgun Parvathaneni)Can you post a qvf where this data is already loaded.....

Re: Create a calculated Pareto dimension involving Select
Phalgun Parvathaneni May 24, 2017 12:49 PM (in response to Sunny Talwar )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!

Sample.qvf 176.0 K

Re: Create a calculated Pareto dimension involving Select
Sibusiso Phumelo May 24, 2017 5:49 PM (in response to Phalgun Parvathaneni)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 May 24, 2017 9:10 PM (in response to Phalgun Parvathaneni)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
Phalgun Parvathaneni May 25, 2017 1:17 AM (in response to Sunny Talwar )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
Phalgun Parvathaneni May 25, 2017 2:00 AM (in response to Phalgun Parvathaneni)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
Jonathan Dienst May 25, 2017 2:31 AM (in response to Phalgun Parvathaneni)If you had searched this site, you would have found
Recipe for a Pareto Analysis – Revisited
And quite a few more...

Re: Create a calculated Pareto dimension involving Select
Sunny Talwar May 25, 2017 5:20 AM (in response to Phalgun Parvathaneni)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"}, Type = {'C'}>} Sales),
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))

Sample.qvf 176.0 K

Re: Create a calculated Pareto dimension involving Select
Sunny Talwar May 25, 2017 9:36 AM (in response to Sunny Talwar )What rows constitutes of the 11,407.56?

Re: Create a calculated Pareto dimension involving Select
Phalgun Parvathaneni May 25, 2017 9:37 AM (in response to Sunny Talwar )Sales should be 11,407.56 under Type C. That is the final result.

Re: Create a calculated Pareto dimension involving Select
Sunny Talwar May 25, 2017 9:39 AM (in response to Phalgun Parvathaneni)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
Phalgun Parvathaneni May 25, 2017 9:43 AM (in response to Sunny Talwar )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
Phalgun Parvathaneni May 25, 2017 11:27 AM (in response to Sunny Talwar )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
Phalgun Parvathaneni May 26, 2017 9:34 AM (in response to Sunny Talwar )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!

Sample1.xlsx 246.4 K











Re: Create a calculated Pareto dimension involving Select
Sibusiso Phumelo May 24, 2017 5:45 PM (in response to Phalgun Parvathaneni)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

Sample.qvf 176.0 K


Re: Create a calculated Pareto dimension involving Select
Sunny Talwar May 26, 2017 11:27 AM (in response to Phalgun Parvathaneni)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.

Sample (1).qvf 256.0 K

Re: Create a calculated Pareto dimension involving Select
Phalgun Parvathaneni May 26, 2017 11:28 AM (in response to Sunny Talwar )Awesome! This is amazing stuff! I can't thank you enough!
