8 Replies Latest reply on Mar 5, 2012 1:56 AM by Andrew Watson

# If with Aggregate

Hi

I read several posts with this subject but I don't achieve the result:

I have this calculated field:

IF

([CodMarca] = "10129" or

[CodMarca] = "10130" or

[CodMarca] = "10147" or

[CodMarca] = "10152" or

[CodMarca] = "10153" or

[CodMarca] = "10154" or

[CodMarca] = "10158" or

[CodMarca] = "10160" or

[CodMarca] = "10161" )

Then

[Total Liquido]*[% Grupo A]

ELSEIF

( [CodMarca] = "10134" or

[CodMarca] = "10148" or

[CodMarca] = "10150" or

[CodMarca] = "10157" or

[CodMarca] = "10162" )

THEN

[Total Liquido]*[% Grupo B]

ELSEIF

( [CodMarca] = "10138" or

[CodMarca] = "10139" or

[CodMarca] = "10149" )

THEN

[Total Liquido]*[% Grupo C]

End

And I need that [Total Liquido] it's a Aggregated. I Tried ATTR in [CodMarca] but does n ´t work.

Thank You

• ###### 1. Re: If with Aggregate

Ricardo,

Having looked at your example the calculation looks like its working [Rappel Calculado] seems to be multiplying the [Total Liquido] by the correct Parameter value for Group A, B and C (and not D as there is no parameter). There seem to be multiple [CodMarca] values to  [Marcas] but only one that ever has [Total Liquido] values. The Grand Totals look ok too.

If I remove the [Marcas] value the aggregation to the groups look ok ?

What result do you want to get?

Cheers

Peter

• ###### 2. Re: If with Aggregate

Hi Peter

Thank you

This is to calculate the amount of discount by group of brand (A, B and C). Each group as a different %.

So I have to IF the several brands to say in what group they belong.

In the end I want to SUM([Total Liquido]) + SUM([Rappel Ano Anterior]) and like this it turns in a Aggregated field.

Thank you once again.

• ###### 3. Re: If with Aggregate

Hi

I have found a solution but know I have a Grand Total Problem: It's not right. Sub Totals work well but not Grand Totals.

The solution is:

IF

(INT( MIN ([CodMarca])) = 10129 or

INT( MIN ([CodMarca])) = 97129 or

INT( MIN ([CodMarca])) = 10130 or

INT( MIN ([CodMarca])) = 10147 or

INT( MIN ([CodMarca])) = 10152 or

INT( MIN ([CodMarca])) = 10153 or

INT( MIN ([CodMarca])) = 10154 or

INT( MIN ([CodMarca])) = 10158 or

INT( MIN ([CodMarca])) = 10160 or

INT( MIN ([CodMarca])) = 10161 )

Then

[Valor Total Liq.]*[% Grupo A]

.

.

.

where

[Valor Total Liq.] it's a aggregated.

Thanks

• ###### 4. Re: If with Aggregate

Hi Ricardo,

It is important to know that grand totals are not necessarily are sum of the values in the rows/columns. Rather, they use whatever the aggregation is on the measure. So for example, if max is the aggregation being used on a measure, the grand total will be the largest value in the column/row, not a sum of the max values. This is a great suggestion for the idea board--allowing the user to decide how the grand total should be aggregated.

-Tracy

1 of 1 people found this helpful
• ###### 5. Re: If with Aggregate

Hi Tracy

Thanks !

So I need to get back to the Beginning of my problem:

I Want to do IF with some Aggregate and some none Aggreagate:

IF [PRODUCT] = "10101" or [PRODUCT]= "10102" THEN SUM(something) * [ParameterA]

ELSE IF [PRODUCT] = "10103" or [PRODUCT]= "10104" THEN  SUM(something) * [ParameterB]

How can I do That in a Calculated Field?

Thank you very Much

• ###### 6. Re: If with Aggregate

Have you tried something like:

sum(IF [Product] = 10101 or [Product] = 10102 then [Something] * [ParameterA] else [Something] * [ParameterB] end )

Andrew

• ###### 7. Re: If with Aggregate

Hi Andrew

Thank you, but it doesn't work.

I Tried MIN(INT([Product] = 10101)) ...

and with SUM, MAX, ATTR and it works on some Products (not all)  but in Grand Totals the results aren't correct.

Thanks

• ###### 8. Re: If with Aggregate

With regards to your Grand Totals the grand total uses the same aggregation as in your calculated field. For example if you use a MIN aggregation the grand total will show the MIN of your selection, if you use a MAX it'll show the MAX, if you choose SUM it'll sum the selection, etc.

It doesn't make sense that what you're seeing works for some products but not for others. Perhaps if you attach the workbook and a brief description of what you're doing and where it fails it'll help with finding a solution.

Before sending this I just had a read higher up the thread and realised Tracy said the exact same thing about the grand totals.

Andrew