4 Replies Latest reply on Feb 18, 2016 7:15 AM by ChandraSheker A

# Date-Calculations

Hi all,

Please Find attachment for the Sample excel file,

My Question is: If, an ID Has Date3, i want to see same date in Date2 and Date1 columns,

If, an ID has Date2, I want to see Same date in Date1,

and If, an ID has Date2 and Date3, I want to see Date2 in Date1.

and If, and ID has Date1 and Date3, I want to see Date3 in Date2.

And Finally I want to Count the Number of IDs, Count of Date1,date2,date3.

Like                                                   500                      300           150      50

Please Help me out in calculating the above because my logic is if an ID has date3 it should have date2 and date1 but not Null.

ID has Date2 it should have date1 but not Null.

Let me know if i am not clear.

Thanks.

• ###### 1. Re: Date-Calculations

Let's try to clarify your logic a bit. Let's call the new Date1 column ActualDate1 and Date2 column ActualDate2. Now taking them one at a time:

ActualDate1

If Date2 has a value, then ActualDate1 is Date2, even if Date1 already has a value.

Otherwise, if Date3 has a value, then ActualDate1 is Date3, even if Date1 already has a value.

Otherwise, ActualDate1 is just the value of Date1.

ActualDate2

If Date3 has a value, then ActualDate2 is Date3, even if Date2 already has a value.

Otherwise, ActualDate2 is just Date2.

Is this correct?

-Steve

• ###### 2. Re: Date-Calculations

I start with putting your data in Tableau and pivoting the three date fields, and I ended up with the ID field, a field called Pivot Field Names (which contained the Date2, Date2 1, and Date 3 fields), and a field called Pivot Field Values.  That's how I was able to duplicate what you have above in Tableau.

At first glance, I'm not sure how I would alter this logic to apply, but there is an article about how to populate values across:  http://kb.tableau.com/articles/knowledgebase/populating-empty-cells-with-zeros-existing-data

I don't usually work with text fields too often, so I'm not sure how this could be altered (if it can be altered) to work with a text field in Tableau, but I hope this is a good starting point.

-Ann-

• ###### 3. Re: Date-Calculations

We have to create 2 calculated fields to get the Date1, Date 2 values indivudually.

1) Name: Date1_New

Syntax:

If not(isnull([Date3])) and  (isnull([Date2])) then [Date3]

elseif (isnull([Date3])) and  not(isnull([Date2])) then [Date2]

elseIf not(isnull([Date3])) and  not(isnull([Date2])) then [Date2]

else [Date1] end

2) Name: Date2_New

Syntax:

If not(isnull([Date3])) then [Date3] else [Date2] end

Best Regards

Kumar

• ###### 4. Re: Date-Calculations

ActualDate1

If Date2 has a value, then ActualDate1 is Date2, even if Date1 already has a value.(((( If there is Actual date1 has value, it shouldn't be altered)))

Otherwise, if Date3 has a value, then ActualDate1 is Date3, even if Date1 already has a value.((( Like above, If there is actual Date1 or Actual Date2 these shouldn't be changed with Date3)))

Otherwise, ActualDate1 is just the value of Date1.

ActualDate2

If Date3 has a value, then ActualDate2 is Date3, even if Date2 already has a value.(((( If we have Date2 already had Value it shouldn't be replaced by Date3))

Otherwise, ActualDate2 is just Date2.