Average of daily sums, dimension problem

[qw_jakub]

Hello,

I am trying to calculate an average of daily sums. The Formula I use is:

=avg(aggr(sum(AMOUNT1), Date))

what I suppose it should do is for each day sum the amounts and then make an overall average of these summs.

This works fine, but when I use it in a table and add a dimension (the SERVICEID), the values are wrong or remain blank (see the attached sample).

But when I filter the dimension elements one by one I get the rigt values.

Do you have any tips, how to make this work in the table?

Thank You

Jakub

[#2]

I recon you have to include SERVICEID in the aggr expression like :

=avg(aggr(sum(AMOUNT1), Date,SERVICEID))

[qw_jakub]

Thanks, this gives the right numbers for each SEVICEID, unfortunately it does give a wrong value for the total.

I'd love to be able to combine these, so the result would for this sample file be:

Total: 346.6

1 : 136.6

2: 175

3: 93.3

[Gysbert_Wassenaar · Accepted solution]

For that you need the dimensionality() function:

=if(Dimensionality()=0,avg(aggr(sum(AMOUNT1), Date)), avg(aggr(sum(AMOUNT1), Date,SERVICEID)))

[qw_jakub]

Thanks a lot! This seems to do the job.

I will test it on my real data now.

Jakub