-
1. Re: Create a calculated Field with 2 different date fields
Anisha Jhamb Jun 20, 2018 1:03 PM (in response to Tapiwa Tapiwa)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
Tapiwa Tapiwa Jun 20, 2018 12:22 PM (in response to Anisha Jhamb)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
Anisha Jhamb Jun 20, 2018 1:08 PM (in response to Tapiwa Tapiwa)Please share a sample workbook.
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
Tapiwa Tapiwa Jun 20, 2018 4:06 PM (in response to Anisha Jhamb)Attached is the sample workbook.
I really appreciate the help.
-
5. Re: Create a calculated Field with 2 different date fields
Tapiwa Tapiwa Jun 20, 2018 7:08 PM (in response to Anisha Jhamb)Attached is the excel spreadsheet
Thank you so much for looking at this.
-
Book to Approve.xlsx 30.7 MB
-
-
6. Re: Create a calculated Field with 2 different date fields
Anisha Jhamb Jun 20, 2018 10:22 PM (in response to Tapiwa Tapiwa)HTH!
The .twbx file you tried to attach was blocked by your organization.
-
7. Re: Create a calculated Field with 2 different date fields
Anisha Jhamb Jun 20, 2018 11:18 PM (in response to Tapiwa Tapiwa)1 of 1 people found this helpfulLooking 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
-
8. Re: Create a calculated Field with 2 different date fields
Tapiwa Tapiwa Jun 21, 2018 8:22 AM (in response to Anisha Jhamb)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.
-
Book to Approve.xlsx 30.7 MB
-
-
9. Re: Create a calculated Field with 2 different date fields
Anisha Jhamb Jun 21, 2018 10:03 AM (in response to Tapiwa Tapiwa)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
Tapiwa Tapiwa Jun 22, 2018 11:42 AM (in response to Anisha Jhamb)Thank you Anisha, this worked for me.
-
11. Re: Create a calculated Field with 2 different date fields
Anisha Jhamb Jun 22, 2018 9:57 PM (in response to Tapiwa Tapiwa)Glad that it worked for you!