11 Replies Latest reply on Jun 22, 2018 9:57 PM by Anisha Jhamb

# Create a calculated Field with 2 different date fields

I have a calculated field Called Current Year Book-to-Approve_Numerator used to provide funded count. To derive this number I use the Shaw Upload date (Funded Count),

SUM(IF YEAR([Shaw_Upload_Date])=YEAR(TODAY()) THEN [Funded Cnt] END)

I also have a calculated field Called Current Year Book-to-Approve_Denominator used to provide Approved count. To derive this number I use the Initial received date (Approved Count)

SUM(IF YEAR([Initial Received Date])=YEAR(TODAY()) THEN [Approved Cnt] END)

These numbers match with the business, now they want a step further to calculate Book to Approve calculated field .  The formula is simple,

Funded Count/Approved Count.

The problem is the 2 fields are calculated using different date fields.   Those numbers do not match what I have on the attached excel spreadsheet. Approved Count is using Initial Received Date while Funded Count is using Shaw Upload date

How to I make that calculation work.  Your help will be much appreciated

• ###### 1. Re: Create a calculated Field with 2 different date fields

IF (YEAR([Shaw_Upload_Date])=YEAR(TODAY()) AND YEAR([Initial Received Date])=YEAR(TODAY())) THEN [Funded Cnt] /[Approved Cnt] END

• ###### 2. Re: Create a calculated Field with 2 different date fields

Thanks Anisha,

I wish I could send you the workbook but it is too large.  The 2 dates are still causing some issues.  I am getting over 2000% with that formula.

Thank you

• ###### 3. Re: Create a calculated Field with 2 different date fields

The output of a formula also depends on the other elements in the view (eg.row/column pills, filters, etc.) and the data granularity

• ###### 4. Re: Create a calculated Field with 2 different date fields

Attached is the sample workbook.

I really appreciate the help.

• ###### 5. Re: Create a calculated Field with 2 different date fields

Thank you so much for looking at this.

• ###### 6. Re: Create a calculated Field with 2 different date fields

HTH!

The .twbx file you tried to attach was blocked by your organization.

• ###### 7. Re: Create a calculated Field with 2 different date fields

Looking at your excel file (input data) and the expected result, i feel that you need a common Date Dimension in your data.

I cant upload your data into the database that i have access to, so, I will try my best to explain the approach i would try.

1. Dump the data from your excel file (Book to Approve) in a table (say, Table A)

2. Create 2 more columns: Shaw Upload Month and Initial Received Month, which will contain only months info based on the corresponding date fields.

3. Create another table for Date Dimension (say, Table B), which will have just one column named Month List with values -> January, February,... upto December

4. Join the 2 tables:

SELECT A.*, B.* FROM A,B WHERE (A.Shaw_Upload_Month = B.Month_List OR A.Initial_Received_Month = B.Month_List)

5. Use this as datasource in Tableau and use the Month_List column in the view and drag the rest of the measures.

I think this should give you correct results.

Regards,

Anisha Jhamb

1 of 1 people found this helpful
• ###### 8. Re: Create a calculated Field with 2 different date fields

I have a view that has both the Shaw upload date and initial received date.  See the attached spreadsheet.  Not clear with what you mean by creating another common Date dimension.

• ###### 9. Re: Create a calculated Field with 2 different date fields

Yes, I agree that you already have 2 date fields in your spreadsheet. But, to get the output like the excel Daily Report.xlsx, you need to align both the dates in the rows, so that you can show the result side by side.

By doing the steps mentioned in my previous reply, you should be able to achieve that.

• ###### 10. Re: Create a calculated Field with 2 different date fields

Thank you Anisha, this worked for me.

• ###### 11. Re: Create a calculated Field with 2 different date fields

Glad that it worked for you!