Level Aware Calculation error - 7 day average with 1 row of data

Hi Team

I am trying to create a table that shows one as at date, the total balance for that date and the average balance for the last [7 or n] days (example in screenshot).

I can create this when I have 7+ days of data showing in the table (screenshot). This gives the correct average of 868,422. However when I filter the table to only show current date (desired output) the average amount recalculates to show the average equal to total balance (incorrect at 907,349).

The formula I used for average is: avgOver(sum({total_balance}), [(rank([{date_as} DESC]))<=7], POST_AGG_FILTER)

When I try to change to PRE_FILTER and remove the sum I get the error ‘Execution order mismatch: PRE_FILTER calculations can’t have PRE_Agg operands’.

I’m sure I am missing something very obvious. Request is: how can I get the table with 1 date row to show total balance of 907,349 and average of 868,422.

Thanks!

1 Like

This is because of your rank calculated field. It also needs to have a calculation level.

Can you add the PRE_FILTER to rank as well?

avgOver({total_balance}, [(rank([ {date_as} DESC],[],PRE_FILTER))<=7], PRE_FILTER)

1 Like