Expression field has Missing data

[Anonymous]

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

[swuehl]

I think you get a grain mismatch here in your advanced aggregation (and you should also consider using an outer aggregation function).

See

Pitfalls of the Aggr function

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.

[Anonymous]

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%

[swuehl · Accepted solution]

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

[Anonymous]

Swuehl.

The third one worked...

=Sum({<Vendor=>} TOTAL<Material,Calendar> Aggr(Sum({<Vendor=>}ShipQty),Material,Calendar,Vendor))* sum([&% Share])

Thank you so much,,,