topic Set Analysis - removing a selection in QlikView

Set Analysis - removing a selection

Wed, 14 Mar 2012 13:24:26 GMT

Hi,

With the assistance of SWeuhl, I have the following formula in my QV doc, which is working well:

= avg (

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

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

,[Metric KPI],Tab,Score))

I need to not consider any selections made within the "Site" field....

Reviewing other examples on the forum, theoretically, all I need to do, is include a "Set Analysis" expression, removing the site selection - ie " {<Site = >} "

Therefore, I thought the following syntax should work:

= avg (

{<Site = >}

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

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

,[Metric KPI],Tab,Score))

Although am not getting the desired results (ie it does not take into account no site selection)

Does anyone have any thoughts on the matter?

Please advise,

Kind regards,

Rich

Set Analysis - removing a selection

CELAMBARASAN — Wed, 14 Mar 2012 13:50:26 GMT

Hi,

Try with this

= avg (

{<Site = >}

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

avg({<Site = >} total< [Metric KPI], Tab > Value * [Element weight]))

,[Metric KPI],Tab,Score))

Try adding the exclusion to the Avg functions inside the aggr.

Celambarasan

Set Analysis - removing a selection

Wed, 14 Mar 2012 14:49:42 GMT

Thanks Celambarasn,

I initially tried this approach, which as you correctly state should work (and does in the sample QV file that I created), but my main system, I only see a blank row...

Must be some sort of display condition that I have created...

I will continue investigating in a different area...

Many thanks!

Set Analysis - removing a selection

Wed, 14 Mar 2012 15:44:19 GMT

I have a conditional statement so that it only returns a value, when the result of the formula matches the "Score" field

It looks like its this part of my syntax that is causing my table not to display the data that it should.

With no site selection made, the data appears correct - but as soon as a site has been selected, then no data is being returned (once included within my IF statement).

---------------------------------------------------------

my current syntax is:

if ( formula = Score, formula, 0)

---------------------------------------------------------

where formula =

avg (

{<Site = >}

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

avg({<Site = >} total< [Metric KPI], Tab > Value * [Element weight]))

,[Metric KPI],Tab,Score))

and Score = the "Score" result in the data

---------------------------------------------------------

Which creates the following:

if(

avg (

{<Site = >}

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

avg({<Site = >} total< [Metric KPI], Tab > Value * [Element weight]))

,[Metric KPI],Tab,Score))

=Score,

avg (

{<Site = >}

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

avg({<Site = >} total< [Metric KPI], Tab > Value * [Element weight]))

,[Metric KPI],Tab,Score))

,0)

--------------------------------------

Anyone with any thoughts, greatly appreciated

Kind regards,

Rich

Re: Set Analysis - removing a selection

Wed, 14 Mar 2012 15:45:39 GMT

QV file for the above...

Re: Set Analysis - removing a selection

Thu, 15 Mar 2012 11:04:42 GMT

does anyone have some time to review this issue for me please?

ie initial calculating formulas works, before its included within an "IF" statement...

Any ideas / tips greatly appreciated...

Kind regards,

Rich