Hi Mac, what do you mean that you want to combine these four date fields? I.e. what would you use to figure out what the combined output would be? Would it be the minimum or maximum of the four date fields? Or perhaps a field that distinguishes records based on the raw combination of the four?
We need more information before we would be able to help. A packaged workbook would also be very helpful.
Here is the scenario;
I have 4 date fields
Now, I want this 4 date to combined to become one date field.
Date1 + Date2 + Date3 + Date4 = Date
Using calculated field.
Sorry for not providing a package workbook, company policy that we never should exposed company information.
Hi Mac, do you really want to add them together? Let's say you have these dates:
Would you want the resulting date to be 10/4/4060?
Otherwise, are you trying to concatenate these dates? In that case, your output would be a string:
Base on raw file there are 4 Date Fields.
Now, I want to consolidate the into one date field, using Calculated Field.
Yes, but you have to have some kind of a guideline for how you want to consolidate them.
For example, let's say I give you four numbers: 5, 7, 12, and 17. Now, go ahead and consolidate them. How will you do that without a consolidation rule?
On the raw file, there were 4 comment date fields, 4 date fields, and ID numbers.
Base on the requirements, I'll have to count the ID number per quarter (4 date fields into 1 date field).
The comment date field are the comments which have code per ID number (e.g. A,B,C,D).
The date field are the dates which the comment are inputted.
Sorry Mac, I don't think I can help without seeing a packaged workbook, or at least fake sample data that has been configured in a similar way to the actual data.
Here is the fake sample workbook.
I've created a sheet base on the raw file using crosstab.
I need to consolidate the 4 date fields to provide a date field which is capturing all the line items base on their ID number.
Example Crosstab output:
Comment Code Q1 Q2 Q3
A 23 1 6
B 10 4 2
C 5 5 1
D 4 15 7
Sample.twbx 35.2 KB
Hi Mac, to get the output you're looking for, you need to un-pivot your data so that it's normalized. This means that you should really only have two columns: Comment Code and Date. Unfortunately, since you have to do multiple pivots, you'll have to do this to the data before it gets into Tableau. Here's the output:
And here's a screenshot of some of the data:
Sample (1).twbx 54.0 KB
Actually the excel file is not pivot, this is the raw data which extracted from the datasource.
I'm to find the custom sql for excel in tableau.
By that I can unpivot this data.