Calculate average over all dimensions instead of only dimensions with values

[pascaldijkshoor]

Hello,

Is it possible to show a 0 value instead of - when there is no data available on certain dimensions (see image below). The reason i need this, is because i want to add another table which calculates the average per asset_nr_linked per activity_start_date. Now when i make this calculation, all values are summed up and divided by the count of asset with value. Instead I need to sum up all values and divide by all assets. The reason that there is no value shown is because there is no activity on that date for the asset.

Formula I use in this table:

Sum({$<SAME_DATE = {'0'}>} aggr (ACTIVITY_END-ACTIVITY_START,ACTIVITY_START,ACTIVITY_END))

+ sum (aggr (frac (EXTRA_TIME_CURRENT_DATE),ACTIVITY_START,ACTIVITY_END))

+ above (sum(aggr(frac(EXTRA_TIME_NEXT_DATE),ACTIVITY_START,ACTIVITY_END)))

Formula I want to use to calculate the average:

avg (aggr (Sum({$<SAME_DATE = {'0'}>} aggr (ACTIVITY_END-ACTIVITY_START,ACTIVITY_START,ACTIVITY_END))

+ sum (aggr (frac (EXTRA_TIME_CURRENT_DATE),ACTIVITY_START,ACTIVITY_END))

+ above (sum(aggr(frac(EXTRA_TIME_NEXT_DATE),ACTIVITY_START,ACTIVITY_END))),ASSET_NR_LINKED,[ACTIVITY_START.autoCalendar.Date]))

[rachel_delany]

This may be best dealt with in the load script, if appropriate.

You can utilise the NullAsValue statement to set the null values to 0 for the necessary fields.

See this link‌ for further details about the NullAsValue statement.