Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
What rows constitutes of the 11,407.56?
Sales should be 11,407.56 under Type C. That is the final result.
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?
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.
It's okay if you don't get the matching result too. Just giving me the expression would be enough. Thanks!
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!
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.
Awesome! This is amazing stuff! I can't thank you enough!