6 Replies Latest reply on Mar 11, 2015 6:50 AM by Shawn Wallwork

# Calculate no of days using datediff between last amount  date and previous amount date

Hi All,

In the attached workbook I like to calculate the day difference between last Rent change and previous Rent change. After that I like to only show prop_code for which the date difference in days is < 365 days.

Thanks

Jagjit

• ###### 1. Re: Calculate no of days using datediff between last amount  date and previous amount date

I looked at this thread very similar but no luck

Regards,

jagjit

• ###### 2. Re: Calculate no of days using datediff between last amount  date and previous amount date

Jagjit welcome to the forums! If I'm understanding correctly I believe the attached workbook will do the trick. I put notes in the caption to help walk you through it. The assignment is a tricky one and requires some Table Calc wizardry, which in turn makes changing/modifying the worksheet difficult. You'll notice that as you add/subtract dimensions from the sheet, you'll break table calcs or adversely affect their values. When this happens just open up the TC and go to the Advance window and sort it out.

The good news is that 9.0 will be out within 60 days, and this particular type of need will be much easier using the new LOD expressions.

Let me know if you need a better explanation, or have questions.

Cheers,

--Shawn

• ###### 3. Re: Calculate no of days using datediff between last amount  date and previous amount date

Thanks Shawn. I managed to get the result in SQL as below and was hoping to get the same in Tableau.

Jagjit

• ###### 4. Re: Calculate no of days using datediff between last amount  date and previous amount date

OK. So why not CREATE VIEW and connect to that?

--Shawn

• ###### 5. Re: Calculate no of days using datediff between last amount  date and previous amount date

It should be easier to do in tableau shouldn't it. Not sure if you had a look at the thread above which is the same scenario and that's answered but does not work for me.

Thanks

• ###### 6. Re: Calculate no of days using datediff between last amount  date and previous amount date

I'm confused. Did you not get what you originally asked for?

In the attached workbook I like to calculate the day difference between last Rent change and previous Rent change. After that I like to only show prop_code for which the date difference in days is < 365 days.

Did you look at the workbook? What doesn't work? Are the returned values wrong? Or are you wanting me to add all the other columns for you? If it's the latter, then you'll learn more if you make an attempt to add all those columns and calcs yourself, and then post the workbook when you get stuck.

By the way, you can take the SQL you've written to produce the screenshot, and stick it in a Custom SQL statement within Tableau, and build out your views. This will be slower than doing it in the database, but will work if you don't have CREATE VIEW permissions.

And to answer your last question: "It should be easier to do it in Tableau, shouldn't it?" Not always, in fact very often it's much easier, more performant, to do it in the database. Tableau is a data visualization software. While it can do a bit of lite ETL, it is most definitely NOT an ETL software. Since you have the SQL skills, I'd suggest you will almost always be better off if you use them to shape your data, then use Tableau to visualize it.

Cheers,

--Shawn

PS: "Do it in the database!" Sounds like a great T-shirt for the TC15.