Use Aggr function twice in calculation?

[Anonymous]

Hello community,

Can I use the Aggr function twice within the same calculation?

Ex: Avg(Aggr(Count(Aggr(If...,Field))))

Note: Message was edited by Community Moderator for clarity.

[Anonymous]

Of course, I could not resolve adding FECHA. But you've helped me a lot with your formula, I just took the FECHA field and added the 0,01.

Avg (aggr (Count (Aggr (If (Sum (VALO)> 0,01, DOC_CLI), DOC_CLI)) * COUNT (DISTINCT DATE), DOC_CLI))

[swuehl]

If you are using

Avg (aggr (Count (Aggr (If (Sum (VALO)> 0,01, DOC_CLI), DOC_CLI)) * COUNT (DISTINCT DATE), DOC_CLI))

what do you want to achieve with this?

I believe returning a value in the THEN as well as the ELSE branch of your if() function will lead to a constant Count(DISTINCT DOC_CLI). In other word, I don't think this will return what you want.

[swuehl]

Maximiliano Carena wrote:

Sorry, I was wrong copying the formula, had put Sum (VALOR) and I copied as is and was Sum (VALO) without the R.

Then I served the function, but I was the same as before, me or just averages the documents that have VALO = zero day, I pulled the index down.

I would also like to understand the logic of your proposal, why have to put 0, 1 and after this DOC_CLI, DATE, what I'm saying to logic when I close the parenthesis and write the name of the fields

Sum(Aggr(If(Sum(VALO) > 0, 1), DOC_CLI, FECHA)) / Count(DISTINCT DOC_CLI)

sorry, I don't really understand your question, could you elaborate a bit more?

you can look at the advanced aggregation like building a virtual, temporary straight table with dimensions

DOC_CLI and FECHA and a single expression,

=If(Sum(VALO) > 0 ,1)

i.e. for every dimension line, calculate Sum(VALO), if this sum is larger than zero, return 1 (else NULL, because there is no definition for the ELSE branch).

Then iterate over all dimension lines and sum the expression values (this is done in the outer aggregation around the aggr() function)

To create the average, divide by the distinct count of DOC_CLI.

Still, not sure if this logic is what you want or need. But if the results of my sample doesn't match your expectation, please exactely describe the deviation from your expectation.

Regards,

Stefan