3 Replies Latest reply on Dec 21, 2018 12:18 AM by Ombir Rathee

# Expanding Days Between Two Dates

Hi all,

I have been struggling with this problem for quite some time.

On a basic level, I have a data set that contains date_from, date_to and a cost_per_day.

I would like to see a line chart of total cost per day.

The challenge is even when i pivot it out, I can get it to work as a count of records - but not as a sum.

I don't want to break it into individual days in the data itself due to size of data - we have around 600,000 rows x number of days in period = astronomically high.

For example, assuming a 2 row data set of:

INPUT

 date from date to cost per day 1/01/2017 5/01/2017 4 3/01/2017 7/01/2017 3

...should convert into a line chart showing:

DESIRED OUTPUT

 date total_cost 1-Jan 4 2-Jan 4 3-Jan 7 4-Jan 7 5-Jan 7 6-Jan 3 7-Jan 3

Any help would be greatly appreciated!

• ###### 1. Re: Expanding Days Between Two Dates

Hi Gab,

One way of doing this is to create a tab in a spreadsheet that has every date that you want in your dataset in a single column.  Then you could pull the date field from this new datasource onto your viz and sum up the cost for every day.

Without providing Tableau the dates in between, it basically thinks these dates don't exist.

Best,

Paul

• ###### 2. Re: Expanding Days Between Two Dates

Are you referring to blending to a date table? If so, how do you blend on a function?

i.e. if I have a date record of 4 Jan, it won't currently line up to any of my dates. It would need some sort of 'between' function...

• ###### 3. Re: Expanding Days Between Two Dates

As Paul suggested you need join your table with calender table. See the approach in attached workbook.