Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
tuftonmc
Contributor III
Contributor III

Set Analysis with Exclusion

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?

6 Replies
swuehl
MVP
MVP

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

tuftonmc
Contributor III
Contributor III
Author

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.

swuehl
MVP
MVP

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)

tuftonmc
Contributor III
Contributor III
Author

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

swuehl
MVP
MVP

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

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

tuftonmc
Contributor III
Contributor III
Author

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?