topic Re: Expression field has Missing data in QlikView

Expression field has Missing data

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Expression field has Missing data

swuehl — Thu, 05 May 2016 17:49:38 GMT

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.

Re: Expression field has Missing data

Anonymous — Thu, 05 May 2016 18:01:48 GMT

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%

Re: Expression field has Missing data

swuehl — Thu, 05 May 2016 18:08:40 GMT

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

Re: Expression field has Missing data

Anonymous — Thu, 05 May 2016 22:50:12 GMT

Swuehl.

The third one worked...

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

Thank you so much,,,