Combination of avg, aggr, Sum TOTAL...grrrrrr

[intervigilium]

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.

• "Verkorte mapping" is the dimension"
• Clientnr_ is the upper dimension in a pivottable.
• Expression is Saldo / Aantal2

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

[vishsaggi]

I am not sure but try this:

= Avg(Aggr(Sum ( TOTAL {< Clientnr_>} (Saldo/Aantal2)) , VerkorteMapping) -

  Aggr(stdev(sum(TOTAL {<Clientnr_>} (Saldo/Aantal2))), VerkorteMapping))

[Gysbert_Wassenaar]

Perhaps like this:

avg(Aggr(nodistinct Sum (Saldo/Aantal2), VerkorteMapping)) - stdev(aggr(nodistinct sum(Saldo/Aantal2), VerkorteMapping))

[intervigilium]

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.

[johnw]

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.

[johnw]

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

[Gysbert_Wassenaar]

Yeah, thanks for pointing that out!

[intervigilium]

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

[johnw · Accepted solution]

This seems to work:

avg(total <VerkorteMapping> aggr(sum(Saldo/Aantal2),Clientnr_,VerkorteMapping))-stdev(total <VerkorteMapping> aggr(sum(Saldo/Aantal2),Clientnr_,VerkorteMapping))

[intervigilium]

Great! That is the solution indeed!

Many thanks for now!!