topic Re: Use Aggr function twice in calculation? in App Development

Use Aggr function twice in calculation?

Anonymous — Mon, 02 May 2016 21:08:07 GMT

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?

swuehl — Mon, 02 May 2016 21:27:18 GMT

Yes, you can.

Re: Use Aggr function twice in calculation?

sunny_talwar — Tue, 03 May 2016 00:50:13 GMT

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?

Anonymous — Tue, 03 May 2016 11:12:23 GMT

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?

swuehl — Tue, 03 May 2016 12:33:23 GMT

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?

Anonymous — Wed, 04 May 2016 13:19:18 GMT

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?

swuehl — Wed, 04 May 2016 16:50:09 GMT

Have a look at

Average – Which average?

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

Re: Use Aggr function twice in calculation?

Anonymous — Wed, 04 May 2016 18:31:34 GMT

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?

Anonymous — Wed, 04 May 2016 19:06:05 GMT

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?

sunny_talwar — Wed, 04 May 2016 19:45:57 GMT

How about this:

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?

swuehl — Wed, 04 May 2016 20:03:14 GMT

Or maybe

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

Re: Use Aggr function twice in calculation?

Anonymous — Wed, 04 May 2016 21:03:40 GMT

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?

sunny_talwar — Wed, 04 May 2016 21:12:36 GMT

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

Re: Use Aggr function twice in calculation?

sunny_talwar — Wed, 04 May 2016 21:16:24 GMT

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?

swuehl — Wed, 04 May 2016 21:25:58 GMT

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?

Anonymous — Thu, 05 May 2016 11:21:52 GMT

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?

Anonymous — Thu, 05 May 2016 11:27:34 GMT

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?

sunny_talwar — Thu, 05 May 2016 14:37:34 GMT

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

Re: Use Aggr function twice in calculation?

swuehl — Thu, 05 May 2016 17:14:36 GMT

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?

Anonymous — Thu, 05 May 2016 19:22:22 GMT

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)