Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

davidcdots
New Contributor

Aggr() result for each segment in the aggregation

Hi! I'm new using Qlik Sense Desktop.

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

Customer_IdVisit_IdProduct_IdCategory_IdInStock
CU11P1C11
CU11P2C10
CU11P3C21
CU11P4C21
CU12P1C11
CU12P2C10
CU12P3C20
CU12P4C21

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

Customer_IdTOTALC1C2
CU162.5% = (3/4 + 2/4) / 250% = (1/2 + 1/2) / 275% = (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!

1 Solution

Accepted Solutions

Re: Aggr() result for each segment in the aggregation

Try this

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


Capture.PNG

8 Replies

Re: Aggr() result for each segment in the aggregation

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

davidcdots
New Contributor

Re: Aggr() result for each segment in the aggregation

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!

Re: Aggr() result for each segment in the aggregation

I tried this in QlikView and this worked for me

=Sum(InStock)/Count(InStock)

Do you really need Aggr() function here?

Re: Aggr() result for each segment in the aggregation

Works in Qlik Sense also

Capture.PNG

davidcdots
New Contributor

Re: Aggr() result for each segment in the aggregation

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!

Re: Aggr() result for each segment in the aggregation

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

davidcdots
New Contributor

Re: Aggr() result for each segment in the aggregation

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:

StoreManufacturerBrandQty
A1X1
A2Y1
B1X1
B2Y2
B3Z1
C1X2

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:

StoreSum(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:

Chart.png

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!

Re: Aggr() result for each segment in the aggregation

Try this

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


Capture.PNG

Community Browser