12 Replies Latest reply on Sep 15, 2016 12:33 PM by Mac Chadwick Ranier Tolentino

combined multiple date field into one date field using calculated field

Hi Guys,

My challenge is to combined all 4 date fields into one date field using calculated field.

Can you help me how formulate this one, I'm running out of ideas.

Thanks,

• 1. Re: combined multiple date field into one date field using calculated field

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.

• 2. Re: combined multiple date field into one date field using calculated field

Hi David,

Here is the scenario;

I have 4 date fields

Date1

Date2

Date3

Date4

Now, I want this 4 date to combined to become one date field.

Example

Date1 + Date2 + Date3 + Date4 = Date

Using calculated field.

Sorry for not providing a package workbook, company policy that we never should exposed company information.

Thanks.

• 3. Re: combined multiple date field into one date field using calculated field

Hi Mac, do you really want to add them together? Let's say you have these dates:

1/1/2015

2/1/2015

3/1/2015

4/1/2015

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:

'1/1/20152/1/20153/1/20154/1/2015'

• 4. Re: combined multiple date field into one date field using calculated field

Hi David,

Base on raw file there are 4 Date Fields.

Now, I want to consolidate the into one date field, using Calculated Field.

• 5. Re: combined multiple date field 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?

• 6. Re: combined multiple date field into one date field using calculated field

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.

• 7. Re: combined multiple date field into one date field using calculated field

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.

• 8. Re: combined multiple date field into one date field using calculated field

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

Thanks,

• 9. Re: combined multiple date field into one date field using calculated field

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:

• 10. Re: combined multiple date field into one date field using calculated field

Hi David,

Actually the excel file is not pivot, this is the raw data which extracted from the datasource.

• 11. Re: combined multiple date field into one date field using calculated field

In that case, you'll definitely have to unpivot it before it gets to Tableau. In case you're using MS SQL server, you might want to look at UNPIVOT. Using PIVOT and UNPIVOT

• 12. Re: combined multiple date field into one date field using calculated field

I'm to find the custom sql for excel in tableau.

By that I can unpivot this data.