1 2 Previous Next 16 Replies Latest reply on Oct 4, 2013 3:01 AM by . Indumon

Calculation of reallocation

Hello,

I would like to do this calculation in order to reallocate sales of the product C in A and B.

The reallocation for the product A is in proportion of the share of A in A+B, and the reallocation of the product B is in proportion of the share of B in B + A

ProductSalesReallocated sales
Product Axx + x/(x + y) * z
Product Byy + y/(y+x)*z
Product Cz0

I have some difficulties because I need values which are not in the lines.

Cam

• 1. Re: Calculation of reallocation

You should be able to write calculations using these fields, regardless of which line they are in.  Can you post a workbook with sample data and what you've tried?  Maybe I am not understanding the problem.

• 2. Re: Re: Calculation of reallocation

My point is that I would like to create the "reallocated sales" field.

• 3. Re: Re: Calculation of reallocation

I understand.  You can separate out the sales numbers using calcs like:

"Product A Sales"

IF attr([Product])="Product A" then Sum(Sales) end

Then, you should be able to write your calculation, using these calculated fields.

"Product A Reallocation" would be:

[Product A Sales] + [Product A Sales]/([Product A Sales] + [Product B Sales]) * [Product C Sales]

I haven't tested this, so there may be a better solution.  I would think calculating this in your data source would be easier, but I could be wrong.

• 4. Re: Re: Calculation of reallocation

Also, I don't know if X is supposed to represent one sales figure, or the SUM of sales for the product.  That will make a difference, as well.

• 5. Re: Re: Calculation of reallocation

Indeed, X is a sum of several sales for the product.

Moreover, product A is in reality a group of products and I cannot use it into a calculated field.

It's not possible to calculate it in my data source because this allocation is at a macro level (sum of product) and not at a product level.

• 6. Re: Re: Calculation of reallocation

If you use a calculation to create your groupings instead of Tableau's grouping option, you can then reference the groups in a calc.  But that would turn into a lot of calculations--maybe someone else has a better approach?

• 7. Re: Re: Re: Calculation of reallocation

I tried your solution in my example (attached) and nothing appears for product reattribution fileds. Any ideas?

• 8. Re: Re: Re: Calculation of reallocation

Nope, I have no idea why this is happening.  Another approach altogether may be called for, I'm not the most experienced user and am trying to teach myself new things too.

• 9. Re: Calculation of reallocation

Thanks a lot Matthew, i'll try to find a solution.

I hope someone else would be able to help me.

• 10. Re: Calculation of reallocation

Hello Camille, I have created one calculated field  Reallocation using the case statements.

Calculation Allocation : Case Attr([Product])

When "Product A" Then

Window_max([Product A sales])+Window_max([Product A sales])/(Window_max([Product A sales])+Window_max([Product B sales]))*Window_max([Product C sales])

When "Product B" Then

Window_max([Product B sales])+Window_max([Product B sales])/(Window_max([Product B sales])+Window_max([Product A sales]))*Window_max([Product C sales])

when "Product C" Then 0

End

Output: Unfortunately I created the workbook in Tableau 8.1, I will upload the workbook after recreate in 8.0

1 of 1 people found this helpful
• 11. Re: Calculation of reallocation

See, there's always someone with a good solution!

• 12. Re: Re: Calculation of reallocation

Here you go! Workbook attached

• 13. Re: Calculation of reallocation

Wah great! Thanks a lot Indumon!

The only little problem is that "Product A", "Product B" and "Product C" are groups so I can't use them in the formula.

Do I have to use "contains()" to group into the calculation field?

• 14. Re: Calculation of reallocation

No Problem. Can you tell me the hierarchy of your data?

My understanding is Product A, B, and C are Product group and there is another level (column) has products of each group, am I right? If yes then we need to change the formula from Window_Max to Window_Sum

1 2 Previous Next