Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I hope someone can help me on this. I have a pivot table that looks like below when exported to excel. My goal for this report is to send the Actual % Share to every vendor. When I clear the vendor selections and selecting only the FY, and FQ and Material, there are missing data (in ?) on the last 3 columns ...Is there something that's missing in my formula.?. If I select a certain vendor, the data are correct.... the empty cells are now correctly filled.
Pls. see table below with its formula.
Thank you in advance.
Type | Calendar | Material | Total Shipments For All Vendor | Vendor | % Share | ShipQty | Target | Variance | Actual % Share | |
A | Q3-2016 | 70-23335 | 11682 | A | 0.4 | 5,297 | 4,673 | 624 | 45% | |
A | Q3-2016 | 70-23335 | 11682 | B | 0.3 | 3,181 | ? | ? | ? | |
A | Q3-2016 | 70-23335 | 11682 | C | 0.3 | 3,204 | ? | ? | ? | |
A | Q3-2016 | 69-23336 | 10000 | A | 0.5 | 4,000 | 5,000 | -1,000 | 40% | |
A | Q3-2016 | 69-23336 | 10000 | B | 0.5 | 6,000 | ? | ? | ? | |
A | Q3-2016 | 80-23356 | 6000 | A | 0.7 | 3,000 | 4,200 | -1,200 | 50% | |
A | Q3-2016 | 80-23356 | 6000 | B | 0.3 | 3,000 | ? | ? | ? | |
Formula | ||||||||||
Total Shipments For All Vendor (calculated dimension) | =Aggr(Sum({<Vendor=>}ShipQty),Material,Calendar) Note : I'm trying to get the sum of ship qty per Material per calendar FY and FQ.. I placed Vendor= on the formula because I don't want to change the value on the Target, Variance and % share when a certain vendor is selected. | |||||||||
ShipQty | =sum(ShipQty) | |||||||||
Target | =Aggr(Sum({<Vendor=>}ShipQty),Material,Calendar)* sum([&% Share]) or Total Shipments for all Vendor * [% Share] | |||||||||
Variance | [ShipQty] - [Target (Qty)] | |||||||||
Actual % Share | =sum(ShipQty) / Aggr(Sum({<Vendor=>}ShipQty),Material,Calendar) or ShipQty / Total Shipments for All Vendor |
It would be easier to help with a small sample file to work with.
If you want to try with the NODISTINCT qualifier:
=Sum({<Vendor=>} Aggr(NODISTINCT Sum({<Vendor=>}ShipQty),Material,Calendar))* sum([&% Share]) |
[not sure about the Sum() as outer aggregation function]
Or if you want to try with adding Vendor to the dimension list:
=Sum({<Vendor=>}Aggr(Sum({<Vendor=>}ShipQty),Material,Calendar,Vendor))* sum([&% Share]) |
or maybe
=Sum({<Vendor=>} TOTAL<Material,Calendar> Aggr(Sum({<Vendor=>}ShipQty),Material,Calendar,Vendor))* sum([&% Share])
I think you get a grain mismatch here in your advanced aggregation (and you should also consider using an outer aggregation function).
See
You could try with adding a NODISTINCT qualifier to the aggr() functions (and then please chose also an appropriate outer aggregation function), though I am not sure if this delivers the correct output (mostly because you have not mentioned what you expect to see).
Or try adding Vendor to the dimensions of the aggr() function.
Hi! Swuehl,
,Pls. see below expected output.... if you could share me the formula adding the non distinct, it would be much appreciated...I'm not really good in advanced and complicated formula particularly on aggr and non distinct function.
tks.
Type | Calendar | Material | Total Shipments For All Vendor | Vendor | % Share | ShipQty | Target | Variance | Actual % Share |
A | Q3-2016 | 70-23335 | 11682 | A | 0.4 | 5,297 | 4,673 | 624 | 45% |
A | Q3-2016 | 70-23335 | 11682 | B | 0.3 | 3,181 | 3,505 | -324 | 27% |
A | Q3-2016 | 70-23335 | 11682 | C | 0.3 | 3,204 | 3,505 | -301 | 27% |
It would be easier to help with a small sample file to work with.
If you want to try with the NODISTINCT qualifier:
=Sum({<Vendor=>} Aggr(NODISTINCT Sum({<Vendor=>}ShipQty),Material,Calendar))* sum([&% Share]) |
[not sure about the Sum() as outer aggregation function]
Or if you want to try with adding Vendor to the dimension list:
=Sum({<Vendor=>}Aggr(Sum({<Vendor=>}ShipQty),Material,Calendar,Vendor))* sum([&% Share]) |
or maybe
=Sum({<Vendor=>} TOTAL<Material,Calendar> Aggr(Sum({<Vendor=>}ShipQty),Material,Calendar,Vendor))* sum([&% Share])
Swuehl.
The third one worked...
=Sum({<Vendor=>} TOTAL<Material,Calendar> Aggr(Sum({<Vendor=>}ShipQty),Material,Calendar,Vendor))* sum([&% Share])
Thank you so much,,,