Ignore empty value in weighted average calculation

[johnnyjohn]

Hi, 

I have the below formula that calculates a weighted average. 

Sum(Total n5_fi_sprdcap_tt_touch * Aggr(Sum(n5_fi_value_EUR), n5_fi_sprdcap_tt_touch)) / Sum(Total Aggr(Sum(n5_fi_value_EUR), n5_fi_sprdcap_tt_touch))

How can I adapt this so that it only calculates the weighted average where n5_fi_sprdcap_tt_touch is not null? At the moment it will calculate including it. 

Thanks

[chris_djih]

my first thought is: build a flag for this condition in the script and put it in a set-analysis.

Load *,
   if (len(trim(n5_fi_sprdcap_tt_touch))=0,1,0) as isNull
resident table;

And then you can exclude it in the Set-Analysis: {<isNull={0}>}.