topic Re: Set Analysis with Exclusion in QlikView

Set Analysis with Exclusion

Anonymous — Wed, 18 Jun 2014 16:12:31 GMT

I simply want to create an expression that averages the sum of two fields (DA_Long + DA_Short), but I would like to EXCLUDES instances where the DA_Long+DA_Short is 0. Alternatively I simply want to average where DA_Long+DA_Short is NOT 0.0.

However, the below does not appear to work.

AVG({$<DA_Long={"<>0.0"}, DA_Short={"<>0.0"}>} DA_Long + DA_Short)

Is there another way of doing this?

Re: Set Analysis with Exclusion

swuehl — Wed, 18 Jun 2014 16:14:39 GMT

=avg( if( rangesum(DA_LONG,DA_Short), rangesum(DA_LONG,DA_Short) )

Re: Set Analysis with Exclusion

Anonymous — Wed, 18 Jun 2014 16:35:00 GMT

Thank you Swuehl

Does RangeSum igonore nulls? I don't want to include records where nulls are interpreted as 0 and the 0 contributes towards the average. I simply want to exclude records where DA_Long+DA_Short are 0 or Null.

Re: Set Analysis with Exclusion

swuehl — Wed, 18 Jun 2014 18:14:37 GMT

The rangesum() will treat NULL evaluated arguments zero values. But I think most important is that the ELSE branch is NULL and won't contribute to the average(). So regardless if DA_Long and / or DA_Short are zero or NULL, as long as the rangesum() is not different from zero, it will not contribute. But, maybe I am misunderstanding your request, instead of the rangesum(), you can use the plain arithmetic sum:

=avg( if( DA_Long+ DA_Short <> 0, DA_Long+DA_Short) )

If you want increase performance, I would suggest creating the sum of your two fields in the script, then use the summed field

LOAD

DA_Long,

DA_Short,

if(DA_Long + DA_Short <> 0, DA_Long + DA_Short, NULL() ) as SummedField,

...

avg( SummedField)

Re: Set Analysis with Exclusion

Anonymous — Wed, 18 Jun 2014 18:26:41 GMT

Thanks swuehl,

I have found an alternative to rangesum, using set analysis where Positions={">0 <0"} which is the same as DA_Long+DA_Short<>0.

AVG({$<DashAssetClass={'EQUITY'}, Position={">0 <0"}>} aggr(SUM(DA_Long + DA_Short), Ladder_Date))

This works for all Dimensions (Issuers).

HOWEVER another expression I want is the RTN (see below) where the numerator is the sum of PnL and the denominator is the AVG I calculated above. The problem is when I select a few Dimensions I can see the chart and the returns. But when I want the entire universe of dimension it does not show the chart. Any idea why that may be?

SUM({$<DashAssetClass={'EQUITY'}>} TDY_PnL_Cash_Accr)/AVG({$<DashAssetClass={'EQUITY'}, Position={">0 <0"}>} aggr(SUM(DA_Long + DA_Short), Ladder_Date))

Re: Set Analysis with Exclusion

swuehl — Wed, 18 Jun 2014 18:37:22 GMT

No sorry, my crystal ball is on vacation this week.

You would need to post more information on your data model and chart setting.

Re: Set Analysis with Exclusion

Anonymous — Thu, 19 Jun 2014 11:23:01 GMT

Thanks swuehl

Apologies for getting detailed on my specific model. I want to try to simplify and I think there are couple of issues.

Firstly, there is a subtilty here in that I want to average DA_Long+DA_Short over dates and I'am not sure this is possible for rangesum. So am I correct in using AVG(aggr(SUM(DA_Long+DA_Short), Ladder_Date))?

At the same time I want to avoid in the avg contribution where DA_Long+DA_Short=0 (or where Position=0).

Secondly and slightly unrelated, in an expression where I have a numerator and a denominator, is it acceptable to have the data set for numerator different from the denominator?