Using aggr() and if() function to return data on the correct row query

[#1]

Hi,

Am trying to build some KPI reports, returning the results of a survey against some pre-defined metrics.

At the moment, when I incorrectly use a straight "avg(Value)" formula, I am able to get my data to appear in the correct layout.

Replicating the above, but using a correct "aggr()" function, I am no longer able to get my desired layout (but am able to get the correct figures).

Please see the attached QV doc...

Does anyone have any thoughts as to how I can overcome this issue?

Please advise,

Kind regards,

Rich

[jagan]

Hi,

Use this expression.

=aggr((avg(Value)),[Metric KPI],Tab, Site)

Regards,

Jagan.

[swuehl]

Maybe like in attached sample?

Just added a NODISTINCT to some aggr() function, added an additional if() clause to limit the results and  added a TOTAL qualifier to avg() in a another spot.You'll see when looking at the expressions.

Hope this helps,

Stefan

[#4]

Hi Stefan,

Many thanks for your response!

Looking at your proposal, its not quite giving me the number that I am looking for.  I was hoping to see the result of the aggr() function to return 3.2 (and not the current 2.6 - which is returning the same result as my straight "avg(Value) formula..

Do you have any additional thoughts?

Kind regards,

Rich

[jagan]

Hi,

Try this expression

=if(Score=3,

avg(aggr((

avg(Value)

),[Metric KPI],Tab, Score))

,0)

Hope it helps you.

Regards,

Jagan.

[#6]

apologies Stefan, your initial proposal was spot on - I was looking in a different table for your results!

Once again, many thanks!

[#7]

Hi,

Just incorporated the above proposal into my main system, and wasn't getting the desired results, despite everything looking spot on in the sample file that I created (please see the tab in red in the attached document)

Adding in additional Metric KPI's, I am now getting the same result appear across 2 different KPI's whilst in reality, I would expect to see 2 different values.

ie in the attached document, both "Commitment" and "Communication" are returning the same value "3", when in reality I would expect to see "3.2" and "2.8" being returned.

Am assuming this is due to the inclusion of the "Total" statement, but unsure how best to fine tune?  Anyone with any thoughts?

Please advise,

Kind regards,

Rich

[swuehl · Accepted solution]

Rich,

I'm a little bit in a hurry, but attached seems to look better, using

=if(Score=3,

avg(total<[Metric KPI],Score> aggr(

avg(total<[Metric KPI],Tab> Value)

,[Metric KPI],Tab,Score))

,0)

pls have a look,

Stefan

[#9]

Many thanks Stefan for taking time to look at the above!  All looking fantastic!

I wish you a very nice weekend,

Kind regards,

Rich