spotfire

SpotFire - taking the average value over 90 days using only non zero values

In Spotfire data canvas I am trying to get an average value over 90 days. but right now its also averaging some zero values that I want it to ignore.

I have attached a screenshot of what a sample table may look like: enter image description here

Here is the current function I am using to get the avg oil prod over 90 days.

avg([OIL_BBLD]) OVER (Intersect([UWI_FORMATTED],LastPeriods(90,[PRODUCTION_DATE])))

how do I either edit this function or create a new function that will avg the oil prod over 90 days and ignore all 0 values.

Solution

  • You can define a column that is equal to [OIL_BBLD] when this is non zero and null otherwise. Nulls are not considered in averages.

    so [OIL_BBLD_2] is:

    case  when [OIL_BBLD]=0 then NULL else [OIL_BBLD] end

    then you can use it in your average.

    Alternatively you can put this definition straight into the expression for the average, but then your result will be undefined (null) for all the rows where [OIL_BBLD] is zero.

    so either:

    avg([OIL_BBLD_2]) OVER (Intersect([UWI_FORMATTED],LastPeriods(90,[PRODUCTION_DATE])))

    or:

    case  
when [OIL_BBLD]!=0 then avg([OIL_BBLD]) OVER (Intersect([UWI_FORMATTED],LastPeriods(90,[PRODUCTION_DATE])))
end