14 Replies Latest reply on Jun 11, 2018 4:33 PM by Deepak Rai

# Dynamic field calculation using dates

Hi,

I want to create a dynamic field calculation using dates.

First of all, I need to calculate date difference as DATEDIFF('year', 'dynamic current quarter end', [DOH])

Is this possible that my quarter end is dynamic? 31/3/2018 for Q1 in 2018, 31/3/2019 for Q1 in 2019.

• ###### 1. Re: Dynamic field calculation using dates

This will always Return you End Date For Current Quarter

The above results in this

and you can get Date Diff using

• ###### 2. Re: Dynamic field calculation using dates

Hi Deepak,

This doesn't work. The calculated field has value zero.

Any suggestion?

• ###### 3. Re: Dynamic field calculation using dates

If You would compare 2018 with 2018, you are likely to get 0

Please Check The Screenshot Below, I am Comparing the Order Year with Today and Getting CORRECT values.

Thanks

Deepak

• ###### 4. Re: Dynamic field calculation using dates

But, I want the date to calculate the years employed from the date of hire.

So if I was hired on 2/12/14 then your years employed until 2018 Q1 is 3.98 years.

Thanks,

Deepika

• ###### 5. Re: Dynamic field calculation using dates

How are you getting 3.98 Years. Can you post some example so that I can see what you are after?

• ###### 6. Re: Dynamic field calculation using dates

So this is the excel output of the number I mentioned

I want to do this calculation in Tableau instead of excel. In excel it uses the function Days 360.

Regards,

Deepika

• ###### 7. Re: Dynamic field calculation using dates

Please Show That Calculation which is giving you 3.98

• ###### 8. Re: Dynamic field calculation using dates

Hi Deepak,

So it is using the yearfrac() function in excel. Also I forgot to mention would need to add an if clause if the employee is terminated you calculate [termination days - Date of hire]

Regards,

Deepika

• ###### 9. Re: Dynamic field calculation using dates

Ok, Now I need your This Data, or you can give fake data. btw...ARE you Comparing against This Date 2/6/18 ?

• ###### 10. Re: Dynamic field calculation using dates

Hi,

I just attached the subset of a data. Let me know if you can help.

Best Regards,

Deepika

• ###### 11. Re: Dynamic field calculation using dates

With This Calc , I am getting This, Your First Value is Not Matching Though, It won't Because you crossed  last Day of Quarter, Rest are Below 3/31/2018

Thnaks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread.

• ###### 12. Re: Dynamic field calculation using dates

Okay, this helps.

Can you help me how to add an if cause in the statement. I want to add condition if term date is mentioned subtract it with that else subtract with quarter?

• ###### 13. Re: Dynamic field calculation using dates

I Hope now it is Done. If It Helps, Pl mark it Helpful and CORRECT to close Thread

Thanks

Deepak

1 of 1 people found this helpful
• ###### 14. Re: Dynamic field calculation using dates

Hi Deepika,

If it Helped, Pl close The Thread Also so that Some one else with similar Question would be benefitted too. The CORRECT button is under my Reply in your This main Thread, not in email.

Thanks

Deepak