IF (YEAR([Shaw_Upload_Date])=YEAR(TODAY()) AND YEAR([Initial Received Date])=YEAR(TODAY())) THEN [Funded Cnt] /[Approved Cnt] END
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.
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
The .twbx file you tried to attach was blocked by your organization.
1 of 1 people found this helpful
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.
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.
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.
Thank you Anisha, this worked for me.
Glad that it worked for you!