8 Replies Latest reply on Oct 25, 2013 4:06 PM by Tracy Rodgers

How to sum up absolute deviations?

Hi

I have an issue in Tableau when it comes to summing up absolute differences. In the example below I have calculated the "Absolute forecast error" by using the formula ABS(SUM([Adjusted forecast DCH (kg)]-[Actual demand (kg)])). This gives the right values for the two items in the table. However, my challenge comes when I remove the items from the rows to get a total absolute difference. The number I would like to see is 389 (sum of the absolute differences) in Absolute forecast error and not 355 as I get (see the second picture)

Best regards

Markus Nosko • 1. Re: How to sum up absolute deviations?

I think ABS() should have to place before SUM()

SUM(ABS([Adjusted forecast DCH (kg)]-[Actual demand (kg)]))

• 2. Re: How to sum up absolute deviations?

Thanks for your advice. However, I have tried this out but this returns the number 5662. In the underlying data I have many rows for the same item (one row per customer). Maybe this has some effect to the result? However, I don’t want to sum up the deviations from the lowest level (customer). I want to sum up the absolute deviations on the values I have for the items (grouped, as in the picture). Any new idea?

• 3. Re: How to sum up absolute deviations?

Hi Marcus,

Please, replace SUM by TOTAL. e.g.  ABS(TOTAL([Adjusted forecast DCH (kg)]-[Actual demand (kg)])) and verify the results.

If you share a workbook with sample data it is easier for us to help you.

Best,

Ramon

• 4. Re: How to sum up absolute deviations?

Hi

Thanks for your suggestion Ramon, but this didn’t work either. I’ve put together a simple workbook. See attachment. The question is basically, how do I get the “Absolute forecast error” to become “500” instead of “300” when removing “Item” from the rows.

Many thanks for the attention so far.

Best regards

Markus

• 5. Re: How to sum up absolute deviations?

Could you give the twbx file and not the tde file.

Chandra

• 6. Re: How to sum up absolute deviations?

Thanks Marcus,

Please, export your workbook as package workbook and share it. As Chandra mention in the above post, we are not able to get the data source with a twb file.

Ramon

• 7. Re: How to sum up absolute deviations?

Oh sorry. My fault. Will this file do?

Best regards

Markus

• 8. Re: How to sum up absolute deviations?

Hi Markus,

Place Item on the level of detail shelf and create a calculated field similar to the following:

if (first()==0) then window_max([Absolute forecast error]) end

Place it on the view, right click on it and select Compute Using>Item.

You'll want to do the same thing for Actual demand and Adjusted forecast using a formula similar to:

if (first()==0) then window_sum(sum([Actual demand (kg)])) end

Hope this helps!

-Tracy