0

I'm evaluating Superset and I want to try to understand if it's possible and how to get different levels of aggregation over the same set of data. I'm working with banking accounts. I'm taking daily snapshots of account's balances, so I have a table with the following structure (very simplified):

account_id date balance week 1 2018-01-01 $5.00 1 1 2018-01-02 $10.00 1 1 2018-01-03 $10.00 1 1 2018-01-04 $10.00 1 1 2018-01-05 $10.00 1 1 2018-01-06 $10.00 1 1 2018-01-07 $10.00 2 1 2018-01-08 $10.00 2 1 2018-01-09 $10.00 2 1 2018-01-10 $10.00 2 1 2018-01-11 $20.00 2 1 2018-01-12 $20.00 2 1 2018-01-13 $20.00 2 1 2018-01-14 $20.00 3 2 2018-01-09 $70.00 2 2 2018-01-10 $70.00 2 2 2018-01-11 $70.00 2 2 2018-01-12 $70.00 2 2 2018-01-13 $5.00 2 2 2018-01-14 $5.00 3

First, I need to calculate the total balance per day. That's pretty straight forward. I can build a Slice using date as Time column and the metric SUM of balance. Using the example data above the results must be:

date total balance week 2018-01-01 $5.00 1 2018-01-02 $10.00 1 2018-01-03 $10.00 1 2018-01-04 $10.00 1 2018-01-05 $10.00 1 2018-01-06 $10.00 1 2018-01-07 $10.00 2 2018-01-08 $10.00 2 2018-01-09 $80.00 2 2018-01-10 $80.00 2 2018-01-11 $90.00 2 2018-01-12 $90.00 2 2018-01-13 $25.00 2 2018-01-14 $25.00 3

Then I need to calculate the weekly average balance per account. This is easy too, because I can use AVG as metric:

week average_balance_per_account 1 $9.17 2 $32.08 3 $12.50

But after that, I want to calculate the average total balance per week. That's the average of daily total balance. In this example:

week average_balance expression 1 $9.17 = (5+10+10+10+10+10)/6 2 $55.00 = (10+10+(10+70)+(10+70)+(20+70)+(20+70)+(20+5))/7 3 $25.00 = ((20+5))/1

Is there any way to calculate this last report using the same table defined before?

dd.
  • 440
  • 1
  • 4
  • 9

1 Answers1

1

The metric you are trying to calculate can be represented as: sum(balance)/count(distinct date); the total daily balance divided by total days. You would then group by week to generate your average balance.

First, create a new metric on your table. Update the SQL Expression to be sum(balance)/count(distinct date).

Second, ensure your week column has been marked Groupable. Another approach would be to use the date column when creating a Slice and setting Time Grain to week.

Aaron Bannin
  • 127
  • 2
  • 9