7 Replies Latest reply on Jun 5, 2015 3:10 AM by kettan

# Create Date to divide data across

i currently have a data set that is a total number by customer. i would like to be able to spread the total sales number across 12 equal months. I have created a few calcluated fields:

Start Date

DATE("1/1/2015")

End Date

DATE("12/31/2015")

Month Period

DATEDIFF('month',[Start Date],[End Date])+1

Monthly Sales

[Est Revenue] / [Month Period]

My goal is to create a viz that shows each of the monthly sales applied to the months.

Any ideas?

• ###### 1. Re: Create Date to divide data across

This seems relatively straightforward. Can you be more specific as to the problem you are encountering? Along with that, if you attach a sample packaged workbook (TWBX), you will be much more likely to find someone willing to help you find a solution.

• ###### 2. Re: Create Date to divide data across

Hi Kevin,

Can you explain more about your requirement and if possible share sample data also to solve asap

-

Sai

• ###### 3. Re: Create Date to divide data across

Attached is a Sample Workbook

• ###### 4. Re: Create Date to divide data across

Thank you for the example. That helps, but was only one of the questions. To repeat: Can you be more specific as to the problem you are encountering? What do you want it to look like, and what are your obstacles?

• ###### 5. Re: Create Date to divide data across

My goal is to be able to assign the monthly breakdown to each month and create a monthly trend chart and blend to another datasource which is monthly data. I have a manually defined Start Date and and manually defined End Date. I basically want it to be able to apply to each month:

Data is at total level

Total Sales = \$120,000

Manually defined Start Date = 1/1/2015

Manually defined End Date = 12/31/2015

Sales per Month: 120000/12 = 10000

1/1/15 - \$10,000

2/1/15 - \$10,000

3/1/15 - \$10,000

.

.

.

12/31/15 - \$10,000

• ###### 6. Re: Create Date to divide data across

Your example does little to help explain your goal and your problem. The sample data set is not related to your calculations in any way, in fact the data set does not even have a date field at all, yet your problem centers around dates. I'm trying to help you, but you don't seem to be trying very hard from your side.

Lacking more to base my answer, I am just guessing here, but in order to create a row for each month, you will need a "scaffold" - a data set that contains the values you want to include in your output. In this case, it needs a record for the first day of each month. I created that, pasted it in and created sheet 2.

I hope that helps. If you want further assistance, please post something more like your real problem, with much more detail.

• ###### 7. Re: Create Date to divide data across

Method #1 in  The Cross Join Collection  might work for you.

A long list of examples are provided beneath it such as:

2015-02-03   Re: Divide a value between months

You might find other solutions that work for you in mentioned question collection.

This collection is huge, because 1) it is often needed and 2) not easy to do in Tableau.

Hopefully Tableau  makes it easier to work with date periods  in a future release.

-oOo-

I like best sample data that includes all data needed, but no more than that. The fewer rows and columns it has, the better. I wish Tableau made a video about this as suggested in  Add video about making simple sample dummy data for questions

Since you use more than one data source, it is important that a sample of each table is shared, preferable in a  packaged workbook,  but attaching them as Excel or text files is also helpful.