Ignore empty value in weighted average calculation

johnnyjohn — Mon, 24 Jan 2022 14:24:00 GMT

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

Re: Ignore empty value in weighted average calculation

chris_djih — Thu, 27 Jan 2022 11:55:42 GMT

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}>}.

topic Re: Ignore empty value in weighted average calculation in App Development

Ignore empty value in weighted average calculation

Re: Ignore empty value in weighted average calculation