1 Reply Latest reply: Apr 15, 2018 9:38 PM by Rachel Delany RSS

    Calculate average over all dimensions instead of only dimensions with values

    Pascal Dijkshoorn

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

      screenshot.3.jpg