Tableau Summing up aggregated data with FIXED

0
votes

Data granularity is per customer, per invoice date, per product type.

Generally the idea is simple:

We have a moving average calculation of the volume per week. MA based on last 12 weeks (MA Volume): window_sum(sum([Volume]),-11,0)/window_count(count([Volume]), -11,0)

We need to see the deviation of the current week vs the MA for that week (Vol DIFF): SUM([Volume])-[MA Calc]

We need to sum up the deviations for a fixed period of time (Year/Month) Basically this should show us whether on average, for a given period of time, we deviate positively or negatively vs the base.

enter image description here

Unfortunately I get errors like:

"Argument to SUM (an aggregate function) is already an aggregation, and cannot be further aggregated." Or "Level of detail expressions cannot contain table calculations or the ATTR function"

Any ideas how I can go around this one?

tableau-apiaggregation
your code is not that clear, anyway the Volume "inside" the SUM is the "MA Volume" of the first formula? if so have you already tried to get rid of the sum since it should be already aggregated? – Fabio Fantoni
No, Volume is data present in the original data. It is the volume against each invoice date occurrence. This is why MA volume sums it up. – Chavdar Gugov
so, could you please update your question with all the calculated field involved – Fabio Fantoni
Hi, there are only 2 calculated fields involved: - [MA Volume], which I now see is called [MA Calc], guess this is what caused the confusion - [Vol DIFF] the question is around how to sum up all values of Vol DIFF for a given Month or Year – Chavdar Gugov