topic Re: Aggregation Function and Advanced Set Analysis in App Development

Aggregation Function and Advanced Set Analysis

Fermin_Takyon — Sat, 16 Nov 2024 06:52:05 GMT

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

Re: Aggregation Function and Advanced Set Analysis

Gabriel — Sat, 12 Jan 2019 19:42:50 GMT

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]))

Re: Aggregation Function and Advanced Set Analysis

robert99 — Sun, 13 Jan 2019 10:21:41 GMT

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?

Re: Aggregation Function and Advanced Set Analysis

Fermin_Takyon — Tue, 15 Jan 2019 01:34:28 GMT

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?

Re: Aggregation Function and Advanced Set Analysis

Fermin_Takyon — Tue, 15 Jan 2019 01:36:26 GMT

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

Re: Aggregation Function and Advanced Set Analysis

robert99 — Tue, 15 Jan 2019 08:58:47 GMT

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

Re: Aggregation Function and Advanced Set Analysis

Fermin_Takyon — Tue, 15 Jan 2019 16:07:04 GMT

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.

Re: Aggregation Function and Advanced Set Analysis

robert99 — Tue, 15 Jan 2019 19:04:59 GMT

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

Re: Aggregation Function and Advanced Set Analysis

Vasudha_Manian — Fri, 01 Feb 2019 15:06:47 GMT

inside aggregation function,

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