Calculate average over all dimensions instead of only dimensions with values

pascaldijkshoor — Wed, 11 Apr 2018 12:08:13 GMT

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

Re: Calculate average over all dimensions instead of only dimensions with values

rachel_delany — Mon, 16 Apr 2018 01:38:04 GMT

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.

topic Re: Calculate average over all dimensions instead of only dimensions with values in App Development

Calculate average over all dimensions instead of only dimensions with values

Re: Calculate average over all dimensions instead of only dimensions with values