Aggr() result for each segment in the aggregation

[Anonymous]

Hi! I'm new using Qlik Sense Desktop.

I have a problem with an expression for a calculation with this data:

Customer_Id	Visit_Id	Product_Id	Category_Id	InStock
CU1	1	P1	C1	1
CU1	1	P2	C1	0
CU1	1	P3	C2	1
CU1	1	P4	C2	1
CU1	2	P1	C1	1
CU1	2	P2	C1	0
CU1	2	P3	C2	0
CU1	2	P4	C2	1

Results I need are, for each visit, the sum of the column InStock divided by the quantity of products, then aggregate using average:

Customer_Id	TOTAL	C1	C2
CU1	62.5% = (3/4 + 2/4) / 2	50% = (1/2 + 1/2) / 2	75% = (2/2 + 1/2) / 2

So, if I use the expression Avg(Aggr(Sum(InStock)/Count(InStock), Visit_Id)), I successfuly got the result for TOTAL Customer, but not for each category. I tried adding the Category_Id in the agregation, but it gives me wrong result for the total since the average takes the results for each Visit and Category.

Is there any way to achieve what I want?

Any help will be very appreciate.

Best regards!

[sunny_talwar]

Is this in a chart with Customer_Id as dimension or do you have Category_Id as your dimension also?

[Anonymous]

Hi! Both, Category_Id and Customer_Id are as dimensions... to be more precisely, my visualization is a PivotTable with Customer_Id as rows and Category_Id as columns.

Regards!

[sunny_talwar]

I tried this in QlikView and this worked for me

=Sum(InStock)/Count(InStock)

Do you really need Aggr() function here?

[sunny_talwar]

Works in Qlik Sense also

[Anonymous]

Thankyou very much Sunny, but the Aggr is needed because the operation is not simply the Sum(InStock)/Count(InStock). I'm pretty sure this works with the provided example but not with real data.

The operation I need is calculate the Sum(InStock) / Count(InStock) for each Visit_Id. Then take every one of the results and calculate the average. This is Avg(Aggr(Sum(InStock)/Count(InStock), Visit_Id)). The problem is that this doesn't work when I add the Category as dimension because the Aggr have only 1 result for each visit, and not for each category.

I apologize if I'm not very clear.

Best regards!

[sunny_talwar]

I won't know what you mean unless you can share an example with me... but may be you want this

Avg(Aggr(Sum(InStock)/Count(InStock), Customer_Id, Category_Id, Visit_Id))

But this is just a guess... I don't really know what you are looking to get

[Anonymous]

After some research, I found this is a case of Aggr() grain mismatch. So I will try to explain my issue using a different approach.

I will use the next straight table for my example:

Store	Manufacturer	Brand	Qty
A	1	X	1
A	2	Y	1
B	1	X	1
B	2	Y	2
B	3	Z	1
C	1	X	2

Now, what I want to calculate is the average participation of each manufacturer by store, so, if I select explicitely the Manufacturer 1, the calculation can be acomplished using the expression Avg(Aggr(Sum(Qty)/Sum(Total<Store> Qty), Store)). Since the Manufacturer 1 is selected, the aggr function will create a temporary table with the result for each store:

Store	Sum(Qty)/Sum(Total<Store> Qty)
A	=1 / 2 = 0.50
B	= 1 / 4 = 0.25
C	= 2 / 2 = 1.00

Then the avg function takes the results and returns (0.5 + 0.25 + 1) / 3 = 0.583

At this point, there is no problem. Problem raises when I try to use this calculation on a bar chart with the Manufacturer as my only dimension and no Manufacturer is selected since I want to see the 3 Manufacturers at the same time, something like this:

Is there any way to achieve what I want? I know that Aggr() needs to have the same dimensions as the outside dimensions, but adding Manufacturer to my expression doesn't give me the expected results.

Any help will be very appreciate.

Best Regards!

[sunny_talwar · Accepted solution]

Try this

Avg(Aggr(Sum(Qty)/Sum(Total <Store> Qty), Store, Manufacturer))