Aggregation Function and Advanced Set Analysis

[Fermin_Takyon]

Hello,

I am having a problem when combining the Aggregated function with advanced set analysis. My variable with the set is as follows:

sum(aggr(only(
{< [Year]=, [Year_Num]= {$(=MAX([Year_Num]))}) >}
[Price] * [Quantity] * [Exchange Rate])

,[%Order_Number]))

This one works fine. The problem arises when I try to calculate sales for the last year, with the following modifier set:

{< [Year]=, [Year_Num]= {$(=MAX([Year_Num])-1)}) >}

It doesn't calculate anything just returns cero. If I dont use the Aggregated function the set works just fine (just a simple sum), but when combining the two it doesn't work. I need the aggregated function because products have different attributes in the product master table and so it double counts these and the resulting value is wrong. Using the aggregate and only function prevents this and returns correct numbers

Thanks for the help! 

Fermin

[Gabriel]

Hi,

Sometimes you might have to put the set modifier outside just after the sum function as well. Try this

sum({< [Year]=, [Year_Num]= {$(=MAX([Year_Num]))}) >}
SUM(aggr(only(
{< [Year]=, [Year_Num]= {$(=MAX([Year_Num]))}) >}
[Price] * [Quantity] * [Exchange Rate]))

,[%Order_Number]))

[robert99]

Hi

I try to avoid using aggr unless I have no other option.

I'm unsure why you are getting the double count. Is it because you have two or more products with the same product number in the product table. So its a many to many join with products to orders?

[Fermin_Takyon]

Yes, that is correcto I have a many to many relationship between orders and products, so these double count. Is there a way to eliminate the double counting so as to avoid the use of Aggr which is very resource consuming?

[Fermin_Takyon]

Thanks!! This works fine! I've tried putting the set analysis in the sum and on the only but not on both. Is there a way to avoid using completely the Aggr and Only function? Because my app is now very slow, there are a lot of calculations and these are just some basic measures 

The problem with my data model is a many to many relationship product and orders

[robert99]

Hi

Did you join the products and order table? In other words join to create one table instead of two (in script)? If so don't do this

Because if you say have a table with Product in one column and a sum based on the order column and two separate tables  (Products & Orders) the total will be correct but the rows will not add to the total in some situations

Say if you have

ProdNum  ProdName

AAA       AAA Parts

AAA    AAA Spares

 If your table includes ProdNum not an issue as long as you don't join the tables in script

If your table reports by ProdName there will be double counting as both AAA Parts & AAA Spares will show the same sum. So the total in QLIK will not add to the total of the ProdNames. There are various ways to overcome this depending on various factors. For example one way is just to only load Products numbers once. Using where not exists

[Fermin_Takyon]

No am not joining the tables together. In fact I have a link table between them because of different levels of granularity. I cannot load Distinct in the Product table because I will loose information we need to show, because a Product needs to account to two Different Group Products, so when I do both metrics seperately the total is OK.

[robert99]

Hi

I'm not going to be any help. I don't know why you need a link table. Why you need to use aggr. Or why the resulting value is wrong. But hopefully someone else can help or you can find a solution

[Vasudha_Manian]

inside aggregation function,

{< [Year]=, [Year_Num]= {"$(=MAX([Year_Num])-1)"}) >} should be within double quotes.