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

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

Fri, 02 Mar 2012 13:24:59 GMT

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

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

jagan — Fri, 02 Mar 2012 13:29:29 GMT

Hi,

Use this expression.

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

Regards,

Jagan.

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

swuehl — Fri, 02 Mar 2012 13:39:10 GMT

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

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

Fri, 02 Mar 2012 14:10:32 GMT

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

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

jagan — Fri, 02 Mar 2012 14:20:02 GMT

Hi,

Try this expression

=if(Score=3,

avg(aggr((

avg(Value)

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

,0)

Hope it helps you.

Regards,

Jagan.

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

Fri, 02 Mar 2012 14:28:41 GMT

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

Once again, many thanks!

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

Fri, 02 Mar 2012 15:03:09 GMT

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

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

swuehl — Fri, 02 Mar 2012 17:04:30 GMT

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

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

Fri, 02 Mar 2012 18:15:42 GMT

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

I wish you a very nice weekend,

Kind regards,

Rich