4 Replies Latest reply on Feb 8, 2018 11:53 AM by Lindsay Reed

Break out individual pay dates from a date range?

Lindsay Reed Feb 7, 2018 12:55 PM

Hi there! I have a payment schedule with the date range it's in effect, how often it's paid, and the amount paid. However, I'd like to calculate how much I'm paying each month and the total of all payments. Here's what my source data looks like:

Expense Type	Frequency	First Payment	Last Payment	Amount
Rent	Monthly	1/1/2016	12/1/2016	1000
Rent	Monthly	1/1/2017	12/1/2017	1100
Rent	Monthly	1/1/2018	12/1/2018	1200
Rent	Monthly	1/1/2019	12/1/2019	1300
Utilities	Quarterly	3/30/2017	12/30/2019	90
Parking	Annually	1/1/2016	1/1/2018	100
Parking	Annually	1/1/2019	1/1/2019	110
Other	One-Time	4/11/2017	4/11/2017	100

But here is what I need:

Payment Date	Expense Type	Amount
1/1/2016	Rent	1000
1/1/2016	Parking	100
2/1/2016	Rent	1000
3/1/2016	Rent	1000
4/1/2016	Rent	1000
6/1/2016	Rent	1000
7/1/2016	Rent	1000
10/1/2016	Rent	1000
11/1/2016	Rent	1000
12/1/2016	Rent	1000
1/1/2017	Rent	1100
2/1/2017	Rent	1100
3/1/2017	Rent	1100
3/30/2017	Utilities	90
4/1/2017	Rent	1100
...	...	...
	Total	50,000

In March 2017 I paid 1200

Any ideas? Thanks!

Rent.twbx 38.5 KB

1. Re: Break out individual pay dates from a date range?

Deepak Rai Feb 7, 2018 3:14 PM (in response to Lindsay Reed)

You need to Follow this;

FAQ: Open & Close Dates

Thanks
Deepak
Like Show 0 Likes(0)

Actions
2. Re: Break out individual pay dates from a date range?

Lindsay Reed Feb 8, 2018 11:19 AM (in response to Deepak Rai)

I'm using PostgreSQL, and everything I've found is only applicable to Excel
Like Show 0 Likes(0)

Actions
3. Re: Break out individual pay dates from a date range?

Deepak Rai Feb 8, 2018 11:42 AM (in response to Lindsay Reed)

It is Applicable to Dates Data and is not Source Specific. I know the Solution to your Problem, and It is bit Lengthy and time consuming so I am not Picking Up, But your Solution Definitely Lies in those FAQs. So, I suggested you to go through.
Thanks
Deepak

Try this:

Split Periods Into Rows Dynamically
Like Show 0 Likes(0)

Actions
4. Re: Break out individual pay dates from a date range?

Lindsay Reed Feb 8, 2018 11:53 AM (in response to Deepak Rai)

I did go through that one. As best I can tell, it requires a separate reference table (Excel) which isn't an option for me. I assure you I have been through that page, and if there's an answer I don't understand it, hence my post.

I appreciate your time and understand if you want to pass.
Like Show 0 Likes(0)

Actions

Go to original post

Tableau Community Forums

Break out individual pay dates from a date range?

1. Re: Break out individual pay dates from a date range?

2. Re: Break out individual pay dates from a date range?

3. Re: Break out individual pay dates from a date range?

4. Re: Break out individual pay dates from a date range?

Sign In or
Create your Account

Actions

More Like This

Sign In or Create your Account

Sign In or
Create your Account