Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Trying so many hours and searched the complete internet....End of story i post it here and probably someone knows it in a few minutes.....
At this moment i didn't make an example qvd because i think someone can correct the syntax without it? Otherwise i make an example (dificult due to included information).
Situation:
In a pivot table the following formula gives the correct information ( average of Saldo / aantal minus the stdev of the same.
This one is working in the pivottable (desired value):
avg(Aggr(Sum(Saldo/Aantal2),Clientnr_,VerkorteMapping))- stdev(aggr(sum(Saldo/Aantal2),Clientnr_, VerkorteMapping))
Desired:
I want the same information on dimensionlevel "verkorte Mapping" and exclude the Clientnr_
The same formule must look like:
avg(Aggr(Sum (TOTAL {<Clientnr_>} (Saldo/Aantal2) , VerkorteMapping)) - stdev(aggr(sum(TOTAL {<Clientnr_> (Saldo/Aantal2), VerkorteMapping)).
From other sources i tried to make something with use of Rangesum, but didnt succeed
Probably many mistakes , but how to accomplish this??
Thanks in advance
This seems to work:
avg(total <VerkorteMapping> aggr(sum(Saldo/Aantal2),Clientnr_,VerkorteMapping))-stdev(total <VerkorteMapping> aggr(sum(Saldo/Aantal2),Clientnr_,VerkorteMapping))
I am not sure but try this:
= Avg(Aggr(Sum ( TOTAL {< Clientnr_>} (Saldo/Aantal2)) , VerkorteMapping) -
Aggr(stdev(sum(TOTAL {<Clientnr_>} (Saldo/Aantal2))), VerkorteMapping))
Perhaps like this:
avg(Aggr(nodistinct Sum (Saldo/Aantal2), VerkorteMapping)) - stdev(aggr(nodistinct sum(Saldo/Aantal2), VerkorteMapping))
Thank you both for trying to find a solution.
Unfortunaly both formula's don't work.
I understand this is difficult without a sample, so i will make one.
When doing a total, the syntax doesn't include {}, and the dimension you're totalling would need to be one in the aggr. Something like this I think:
avg(aggr(sum(total <Clientnr_> Saldo/Aantal2),Clientnr_,VerkorteMapping)) - stdev(aggr(sum(total <Clientnr_> Saldo/Aantal2),Clientnr_,VerkorteMapping))
But don't do that. It's just a waste of effort to include the Clientnr_ in the aggr only to remove it again with a total <Clientnr_>. I think gysbert has the correct answer.
Edit: Heh, apparently not. Waiting for the example, then.
I think you just have some missing parentheses? Maybe that's the only problem.
avg(aggr(nodistinct sum(Saldo/Aantal2), VerkorteMapping)) - stdev(aggr(nodistinct sum(Saldo/Aantal2), VerkorteMapping))
Yeah, thanks for pointing that out!
Hi all,
very friendly of you all helping me
I made a example with some data.
Needed:
I want a pivottable with "saldo", Aantal2 and (Saldo/Aantal2).
I want to color backgrounds of Saldo/Aantal2 when it is lower or higher dan 1 st dev.
That is why i need that value in the pivottable, unrelated to the clientnr_.
When i make a seperate table, without the clientnr as dimension, i get the right results. So the question: how can i make that formule undepeding from the clientnr_......
Hope is all clear now
This seems to work:
avg(total <VerkorteMapping> aggr(sum(Saldo/Aantal2),Clientnr_,VerkorteMapping))-stdev(total <VerkorteMapping> aggr(sum(Saldo/Aantal2),Clientnr_,VerkorteMapping))
Great! That is the solution indeed!
Many thanks for now!!