Total sum of category link to another one

[Tenuki]

Hello everyone,

I would like to create a mesure that sum the sales for 3 ProductCategories if my order have the productcatgorie = 410.

I have try this :

Sum(If(Aggr(Count(DISTINCT {<ProductCategory={410}>} OrderID), OrderID) > 0, Sum({<ProductCategory={411, 442, 452}>} Sales), 0))

I had this error :

 nested aggregation not allowed

Then I try with a variable that get all orderID with the Category 410. 
And try this :
Sum(If(WildMatch(OrderID, '$(vFact410)'),
If(Match(ProductCategory, 411, 442, 452), Sales, 0), 0))

But it didn't work and show 0 for each orderlineID.

The Category are link to the orderdetail, with many lines that have a category.
Should I take orderlineID instead of orderID ? 

Hope my request is clear, many thanks for your help

[Oleg_Troyansky · Accepted solution]

Hi there,

In order to avoid the nested aggregation error, change the order of your functions. Both IF and the result should be inside of your aggr:

Sum(

     AGGR(

               If(Count(DISTINCT {<ProductCategory={410}>} OrderID), OrderID) > 0,

               Sum({<ProductCategory={411, 442, 452}>} Sales), 0)

     )

)

This formula can get quite heavy though, I'd try to replace it with advanced Set Analysis:

Sum({<ProductCategory={411, 442, 452}, OrderID=P(<{<ProductCategory={410}>}  )>} Sales)

This formula should return sum of Sales for the three categories for Orders that are available with the Product Category = 410. I think it should work better for you.

Allow me to invite you to my Qlik Expert Class that I'll be teaching in Vienna, Austria on September 22-24. I will be teaching advanced data modeling, advanced scripting, performance optimization, and advanced aggregation and Set Analysis techniques that cover some of these challenges. You will learn the most advanced Qlik methodologies that will help you solve tough problems like this one.

Cheers,

Oleg Troyansky

[Tenuki]

Hello @Oleg_Troyansky many thanks for your quick answer.

The first mesure didn't work, I don't know why. Second one work, but I need to check if the sum match my data.

I will ask my manager for the invitation, thank you.