2 Replies Latest reply on Dec 5, 2017 6:55 AM by Peng Chen

Passing variables from one data source to another

Peng Chen Dec 4, 2017 8:40 AM

Hi dear community,

Would you pls help me on this case?

I have 2 data source,

Data source 1 stores a version with name and start and end date

version	start	end
a	11/09/2017	20/12/2017
b	21/12/2017	03/02/2018
c	06/02/2018	07/05/2018

Data source 2 store people's holiday

Name	date	holiday
aaa	11/10/2017	1
aaa	11/12/2017	0.5
...

I wish to calculate each person's actual working day = (workday between start and end in version table) - sum(holiday field in holiday table who's date is between the selected version's start and end date)

I have attached the tableau sheet here. Pls let me know if this is doable in tableau or not, if not, I might create a script to generate this date in the database.

Thanks a lot for helping!

capacity_calculate.twbx 50.2 KB

1. Re: Passing variables from one data source to another

swaroop.gantela Dec 4, 2017 5:56 PM (in response to Peng Chen)
Peng,

If possible, you could try adding a third sheet with just a list of all possible days,
as described here:
CROSS JOIN with Tableau's join dialog

In your project, you could make a join as shown in attached graphic
connecting all version to all possible dates, and then left joining to holidays.

Then you could count holidays using:
{ FIXED [Version],[Name]:SUM([Holiday])}

and get the subtracted amount using:
MIN([workday_in_version])-SUM(ZN([holiday in version]))

Please see attached workbook in Forum post.

254099hol.twbx 33.1 KB

254099join.png 21.1 KB
Like Show 0 Likes(0)

Actions
2. Re: Passing variables from one data source to another

Peng Chen Dec 5, 2017 6:55 AM (in response to swaroop.gantela)

Hi Swaroop,
Hope I call you right. Thanks so much to show this idea. I does works and unlock me a lot more possibilities!
Like Show 1 Likes(1)

Actions

Go to original post

Tableau Community Forums

Passing variables from one data source to another

1. Re: Passing variables from one data source to another

2. Re: Passing variables from one data source to another

Actions

More Like This