How many days did I work there? (DATEDIFF)

While building my resumé in Tableau I had this idea to make it simple for anyone to see how many time I spent in each one of my jobs. I was just a starter in Tableau so I took that as a good initial challenge.

After some online research (meaning Googleing) I found that Tableau has an expression to calculate difference between dates: DATEDIFF.

As I was building my own dataset (my resumé in Excel) I was then able to add to each employer the day I started in and the day I ended in those companies. Then the calculation was easy:

DATEDIFF(‘day’,[Start Date],[End Date],’sunday’)

Translation to humans: Tableau please count the number of days between the first date [Start Date] and [End Date]. BTW please start the week on Sunday.

So my online resumé was great and dynamic, people can click and check what I accomplished in each year. I really loved it.

But then I realized that the number of days for my current employer was not growing up. And there was a simple reason for that: I wasn’t updating my dataset everyday. I am not that crazy (yet).

So I had to fix it (and I am really good on fixing stuff). I just had to add a special condition for my current employer in order to make Tableau use today’s date as the End Date, making it dynamic. So here is how I did my check:

IF [Employer] = “Inter-American Development Bank” THEN
DATEDIFF(‘day’,[Start Date],TODAY(),’sunday’)
ELSE
DATEDIFF(‘day’,[Start Date],[End Date],’sunday’)
END

Translation to humans: Tableau if you are counting the days regarding Inter-American Development Bank, please count it between the first date [Start Date] and TODAY (whenever Today is) and the week starts on Sunday. For all others just count the number of days between the first date [Start Date] and [End Date] and also start the week on Sunday.

 And that’s it. I hope you learned something about Calculations today with this post.

Note to humans: Yes I speak with Tableau! I speak with Tableau A LOT!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s