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.

[swuehl]

Or maybe

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

[Anonymous]

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?

[sunny_talwar]

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

[sunny_talwar]

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

[swuehl]

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

[Anonymous]

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?

[Anonymous]

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

[sunny_talwar]

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

[swuehl]

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?

[Anonymous]

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)