
Re: Use Aggr function twice in calculation?
Stefan Wühl May 2, 2016 5:27 PM (in response to Maximiliano Carena)Yes, you can.

Re: Use Aggr function twice in calculation?
Sunny Talwar May 2, 2016 8:50 PM (in response to Maximiliano Carena)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?
Maximiliano Carena May 3, 2016 7:12 AM (in response to Sunny Talwar )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?
Stefan Wühl May 3, 2016 8:33 AM (in response to Maximiliano Carena)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?
Maximiliano Carena May 4, 2016 9:19 AM (in response to Stefan Wühl )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?
Stefan Wühl May 4, 2016 12:50 PM (in response to Maximiliano Carena)
Re: Use Aggr function twice in calculation?
Maximiliano Carena May 4, 2016 3:06 PM (in response to Stefan Wühl )
Re: Use Aggr function twice in calculation?
Sunny Talwar May 4, 2016 3:47 PM (in response to Maximiliano Carena)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?
Stefan Wühl May 4, 2016 4:03 PM (in response to Sunny Talwar )Or maybe
Sum(Aggr(If(Sum(VALO) > 0, 1), DOC_CLI, FECHA))







Re: Use Aggr function twice in calculation?
Maximiliano Carena May 4, 2016 2:31 PM (in response to Sunny Talwar )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?
Maximiliano Carena May 4, 2016 5:03 PM (in response to Maximiliano Carena)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 May 4, 2016 5:12 PM (in response to Maximiliano Carena)I think you need to add FECHA as a dimension to your inner aggregation

Re: Use Aggr function twice in calculation?
Sunny Talwar May 4, 2016 6:07 PM (in response to 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))

Re: Use Aggr function twice in calculation?
Maximiliano Carena May 5, 2016 7:21 AM (in response to Sunny Talwar )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?
Sunny Talwar May 5, 2016 10:37 AM (in response to Maximiliano Carena)So the issue is not resolved by adding FECHA/DATE in your inner aggregation?

Re: Use Aggr function twice in calculation?
Maximiliano Carena May 5, 2016 3:33 PM (in response to Sunny Talwar )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?
Stefan Wühl May 5, 2016 4:19 PM (in response to Maximiliano Carena)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?
Stefan Wühl May 4, 2016 5:32 PM (in response to Maximiliano Carena)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

comm215304.qvf 176.0 K

Re: Use Aggr function twice in calculation?
Maximiliano Carena May 5, 2016 7:27 AM (in response to Stefan Wühl )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?
Stefan Wühl May 5, 2016 1:14 PM (in response to Maximiliano Carena)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?
Maximiliano Carena May 5, 2016 3:22 PM (in response to Stefan Wühl )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?
Stefan Wühl May 5, 2016 4:25 PM (in response to Maximiliano Carena)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




