22 Replies Latest reply: May 5, 2016 4:25 PM by Stefan Wühl

# Use Aggr function twice in calculation?

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.

• ###### Re: Use Aggr function twice in calculation?

Yes, you can.

• ###### Re: Use Aggr function twice in calculation?

Depends on what you are trying to do. If you want to know if the syntax is allowed, yes it is. But what exactly do you need this for? May be share a sample with your expected output

• ###### Re: Use Aggr function twice in calculation?

Ahh, I thought it was wrong and could not be used 2 times, as it gave me error, invalid function. I'll keep trying and if not inform you and explain the function and related fields.

Thanks you.

• ###### Re: Use Aggr function twice in calculation?

Maybe we can help you better if you post the full expression.

Advanced aggregation always shows a single expression and one or more dimension fields as arguments to aggr() function.

• ###### Re: Use Aggr function twice in calculation?

Hi swuehl,

Step formula here where I have the inchoerencia: Avg (Aggr (Count (Aggr (If (Sum (VALO)> 0, DOC_CLI), DOC_CLI)) * COUNT (DISTINCT DATE))).

So yes I instead works: Count (Aggr (If (Sum (VALO)> 0, DOC_CLI), DOC_CLI)) * COUNT (DISTINCT DATE)

I need to average this.

This is why we consult them if they could include two Aggr, how can I fix it?

• ###### Re: Use Aggr function twice in calculation?

Have a look at

Average – Which average?

So, what is the internal grouping entity in your case?

• ###### Re: Use Aggr function twice in calculation?

I hope to be more explanatory, you can not clearly see the solution in the link you shared me.

• ###### Re: Use Aggr function twice in calculation?

Sum(Aggr(Count(Aggr(If(Sum(VALO) > 0, DOC_CLI), DOC_CLI)) * Count(DISTINCT DATE), DOC_CLI))

or this:

Sum(Aggr(Count(Aggr(If(Sum(VALO) > 0, DOC_CLI), DOC_CLI, FECHA)) * Count(DISTINCT DATE), DOC_CLI))

• ###### Re: Use Aggr function twice in calculation?

Or maybe

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

• ###### Re: Use Aggr function twice in calculation?

Hi Sunny, Step formula here where I have the inchoerencia: Avg (Aggr (Count (Aggr (If (Sum (VALO)> 0, DOC_CLI), DOC_CLI)) * COUNT (DISTINCT DATE))).

So yes I instead works: Count (Aggr (If (Sum (VALO)> 0, DOC_CLI), DOC_CLI)) * COUNT (DISTINCT DATE)

I need to average this.

This is why we consult them if they could include two Aggr, how can I fix it?

• ###### Re: Use Aggr function twice in calculation?

Dear Thanks Sunny and Swuehl for your time, could generate a formula for the KPI with the Avg (Aggr ... function as follows:

Avg ( aggr (Count (Aggr(If(Sum(VALO) > 0, DOC_CLI), DOC_CLI))*Count (distinct FECHA), DOC_CLI))

I added what's in Red to the original formula I passed them.

But on average I still counting DOC_CLI with zero VALO not know why, if determined Sum (VALO)> 0

Finally, something happens on the basis of this contradiction?

• ###### Re: Use Aggr function twice in calculation?

I think you need to add FECHA as a dimension to your inner aggregation

• ###### Re: Use Aggr function twice in calculation?

The reason is that you are checking Sum(VALO) > 0 on not just your 1st dimension,but on FECHA and 1st dim

Avg(Aggr(Count(Aggr(If(Sum(VALO) > 0, DOC_CLI), DOC_CLI, FECHA))*Count (Distinct FECHA), DOC_CLI))

• ###### Re: Use Aggr function twice in calculation?

Sunny!, I added this but I went a little higher, the closest number I could achieve as follows:

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

I thought I was going to fix this by adding DATE on the inner agregation

Do you recommend any article where I explain clearly how they should be structures functions?

• ###### Re: Use Aggr function twice in calculation?

So the issue is not resolved by adding FECHA/DATE in your inner aggregation?

• ###### Re: Use Aggr function twice in calculation?

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

• ###### Re: Use Aggr function twice in calculation?

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.

• ###### Re: Use Aggr function twice in calculation?

What do you expect to get for your sample data? 1?

I still think you could calculate this like

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

edit: Attached sample QVF

• ###### Re: Use Aggr function twice in calculation?

swuehl, I have a lot of data, I expect you to tell me how much DATE on average than DOC_CLI have VALO greater than zero.

It gives me a number, such as 1.006752

• ###### Re: Use Aggr function twice in calculation?

Have you looked at the sample file I've attached to my previous post?

Can you tell if this is showing what you expect to see, or if not, what exactely deviates from your expectation?

• ###### Re: Use Aggr function twice in calculation?

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)

• ###### Re: Use Aggr function twice in calculation?

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