14 Replies Latest reply on Feb 1, 2019 1:30 PM by Christopher Cheng

# Distribute an Amount for the 12 Months after the date

Hi all,

I am having trouble trying to figure out a solution for trying to distribute an amount over the 12 months after the included date.

It would look something like this (but would prefer to have it on a line graph)

Included Date: 12/1/2020, Amount: 42,000

Date of Dispersed Amount
Dispersed Amount
1/1/20213,500
2/1/20213,500
3/1/20213,500

Etc, Etc.

Is this possible to do straight into Tableau? As in with a formula or calculation? Or do I have to do some manual work within an excel sheet (which would NOT be preferred, but will do if all else fails)?

I've looked at different forum discussions, but could not find a solid answer. Let me know if you need more detail.

• ###### 1. Re: Distribute an Amount for the 12 Months after the date

Does this help?

To achieve the above, I have first created a new tab in your data source which has the list of numbers from 1 to 12.

You can then use a calculated join as shown below to join the data.

Then create the below calculated fields:

Dispersed Date: DATEADD("month",[Month Number],[Target Delivery Date])

Dispersed Amount: [Amount]/{ FIXED [Project ID]:COUNT([Month Number])}

The updated data file and a 2018.3 workbook is attached. Hope this helps.

2 of 2 people found this helpful
• ###### 2. Re: Distribute an Amount for the 12 Months after the date

I don't get the joining part.

Whenever I try to put 1 in a join calculation, I get an error.

• ###### 3. Re: Distribute an Amount for the 12 Months after the date

You need to do a calculated join. Click on the dropdown and select the last option and then enter 1 in the window.

• ###### 4. Re: Distribute an Amount for the 12 Months after the date

Hari..Awesome!!!

• ###### 5. Re: Distribute an Amount for the 12 Months after the date

Yes, I am doing exactly that, but it gives me an error. It says "Unable to do join relation"

• ###### 6. Re: Distribute an Amount for the 12 Months after the date

Let me know the version of Tableau you are using. Am wondering if that version is allowing the join or not.

• ###### 7. Re: Distribute an Amount for the 12 Months after the date

Now that you mention that, I just noticed that version 2018.3.3 is out. Let me update and will let you know how it goes.

• ###### 8. Re: Distribute an Amount for the 12 Months after the date

Also another question while I'm updating:

I noticed that you example is showing it by the individual project ID I have displayed. Is there a way to have it sum? As you can see, there are multiple dates that start in December 2020, so what I would like to see it all of January 2021 added together, not just by one project.

• ###### 9. Re: Distribute an Amount for the 12 Months after the date

You can remove the Project ID from the chart.

• ###### 10. Re: Distribute an Amount for the 12 Months after the date

Update on the Join: After updating to the latest Tableau version, the join using a calculated join seems to be working now.

As for the sum, I did take off project ID at first, but it didn't seem to change anything.

• ###### 11. Re: Distribute an Amount for the 12 Months after the date

I did some more investigating and from just taking off the project ID it seems like that amounts are totaling up just fine.

Thank you for your help Hari!

• ###### 12. Re: Distribute an Amount for the 12 Months after the date

You are welcome.

• ###### 13. Re: Distribute an Amount for the 12 Months after the date

You Rock Hari!!!

• ###### 14. Re: Distribute an Amount for the 12 Months after the date

You'll have to use a secondary datasource to blend with your main one to create the future dates.  I recommend watching this video on Data Scaffolding form Joe Mako.