topic Re: Issue with expression with differing dimensions in QlikView

Issue with expression with differing dimensions

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

I have an expression which calculates the percentage of class 2 and waste generated in an individual production run and presents this in a chart.

Run	Product	Category	Variety	Class 1 count	Class 2 count	Waste count	total count	Class 2 %	Waste %
210969	PEAR	TOPFRUIT	CONFERENCE	2574	0	11	2585	0.00%	0.43%
210974	PEAR	TOPFRUIT	CONFERENCE	12768	0	125	12893	0.00%	0.97%
210975	PEAR	TOPFRUIT	CONFERENCE	6636	0	81	6717	0.00%	1.21%
211019	PEAR	TOPFRUIT	CONFERENCE	72	0	0	72	0.00%	0.00%
211029	KIWI	STONEFRUIT	_	9000	0	0	9000	0.00%	0.00%
211030	PEAR	TOPFRUIT	CONFERENCE	4392	0	0	4392	0.00%	0.00%
211038	PLUM	STONEFRUIT	ANGELENO	19440	375	500	20315	1.85%	2.46%
211043	PEPPER RED	PEPPER	_	750	0	45	795	0.00%	5.66%
211051	APPLE	TOPFRUIT	GRANNY SMITH	6000	138	0	6138	2.25%	0.00%
211053	PEAR	TOPFRUIT	CONFERENCE	8208	0	250	8458	0.00%	2.96%
TOTAL				69840	513	1012	71365	0.72%	1.42%

Run	Product	Category	Variety	Class 1 count	Class 2 count	Waste count	total count	Class 2 %	Waste %
210969	PEAR	TOPFRUIT	CONFERENCE	2574	0	11	2585	0.00%	0.43%
210974	PEAR	TOPFRUIT	CONFERENCE	12768	0	125	12893	0.00%	0.97%
210975	PEAR	TOPFRUIT	CONFERENCE	6636	0	81	6717	0.00%	1.21%
211019	PEAR	TOPFRUIT	CONFERENCE	72	0	0	72	0.00%	0.00%
211029	KIWI	STONEFRUIT	_	9000	0	0	9000	0.00%	0.00%
211030	PEAR	TOPFRUIT	CONFERENCE	4392	0	0	4392	0.00%	0.00%
211038	PLUM	STONEFRUIT	ANGELENO	19440	375	500	20315	1.85%	2.46%
211043	PEPPER RED	PEPPER	_	750	0	45	795	0.00%	5.66%
211051	APPLE	TOPFRUIT	GRANNY SMITH	6000	138	0	6138	2.25%	0.00%
211053	PEAR	TOPFRUIT	CONFERENCE	8208	0	250	8458	0.00%	2.96%
				69840	513	1012	71365	0.72%	1.42%

Expression -

Sum(Aggr(Sum({<Prod_Class -={'Class 1'}>}FPInnerCount),ProductionRun,Product,Prod_Class))/Sum(TOTAL<Product>Aggr(Sum({<Prod_Class>}FPInnerCount),ProductionRun,Product,Prod_Class))

This expression works perfectly if I use the product as a dimension and calculates the percentage of class 2 and waste only against the runs for that category.

However if I use the category or variety as a dimension instead I get the percentage calculated against all runs regardless of category or variety (i.e.; uses class 2 figure for cucumber and divides by total count for every run suitable for filter).

I have changed the 'Product' in the above expression for 'Category' and 'Variety' but still received the same results.

Any ideas?

Re: Issue with expression with differing dimensions

sunny_talwar — Fri, 08 Apr 2016 16:38:04 GMT

Would you be able to share a sample? with expected output?

Re: Issue with expression with differing dimensions

stig1984 — Mon, 11 Apr 2016 09:23:38 GMT

Please see stripped out sample attached.

A good example for working with on category is Cucumber - this only has one product within it (Cucumber whole) which means that it should return the same result for the product and the category (without filters both should return 1.91% class 2 but category currently returns 0.05%)

Re: Issue with expression with differing dimensions

sunny_talwar — Mon, 11 Apr 2016 13:34:12 GMT

Not sure how this behave for other category, but works for Cucumber:

Sum(Aggr(Sum({<Prod_Class -={'Class 1'}>}FPInnerCount),Category,ProductionRun,Prod_Class))/

Sum(DISTINCT Aggr(NODISTINCT Sum(TOTAL<Product>Aggr(Sum({<Prod_Class>}FPInnerCount),Category,ProductionRun,Prod_Class)), Category, Product))

Re: Issue with expression with differing dimensions

jonathandienst — Mon, 11 Apr 2016 13:42:05 GMT

Simplifying your expression may help:

Sum(Aggr(

Sum({<Prod_Class -={'Class 1'}>} FPInnerCount)

Sum({<Prod_Class -={'Class 1'}>} TOTAL <Product> FPInnerCount),

ProductionRun, Product, Prod_Class))

and assuming that works, then for category:

Sum(Aggr(

Sum({<Prod_Class -={'Class 1'}>} FPInnerCount)

Sum({<Prod_Class -={'Class 1'}>} TOTAL <Category> FPInnerCount),

ProductionRun, Category, Prod_Class))