6 Replies Latest reply on Feb 2, 2015 7:13 AM by Dan Zehner

# Data values change by date

Hello! I'm new to Tableau and I'm having trouble wrapping my head around how to calculate a specific value for a piece of data based on the date. Here's what I'm trying to do:

Inputs

Issue IDDPUClose DateResidual
1234560.53/1/150.3

I'm trying to make a table in Tableau that shows the DPU as 0.5 for all dates before the Close Date, and the Residual Value of 0.3 after the Close date. How do I relate this in a calculated field? I have 100,000+ lines of data that need this calculated, so it will have to do the calculation from my raw data.

• ###### 1. Re: Data values change by date

Have your tried some like:

IF [Date Field] < [Close Date]

THEN [DPU]

ELSE [Residual]

END

If this doesn't work trying mocking up an example and packaged workbook

• ###### 2. Re: Data values change by date

Thanks! So for the [Date Field] table, I would just mock up some dummy dates to compare against?

• ###### 3. Re: Data values change by date

I'm not sure I understood the original question based on your response. From your original post it sounds like you have a field that has dates and a field for close date. This is where I made the assumption that there was a Date Field. Could you please explain a little more if this is not the case.

• ###### 4. Re: Data values change by date

Grayson,

I've attached my source data and a workbook where I tried doing the calculated field you suggested earlier. I created a table of dates called Current Month to have dates to compare the Closure Date by, and then calculated the month off each date to relate those two tables. For this example I'm assuming the residual DPU to be 0.3 as a constant. I also need the residual DPU to stay constant for 3 months after the Closure Date and then go to 0 (I forgot about that in my original post, sorry). Here's the calculation I tried for DPU by Month:

if ATTR([Sheet1 (Current Months)].[Current Month])<attr([Clse Dt])

then attr([DPU])

elseif month(attr([Clse Dt]))<month(ATTR([Sheet1 (Current Months)].[Current Month]))+3

then 0.3

elseif month(attr([Clse Dt]))>month(ATTR([Sheet1 (Current Months)].[Current Month]))+3

then 0

end

Hopefully that helps clear up what I'm getting at a little better! Thanks for the help!

• ###### 5. Re: Data values change by date

I figured it out! I just added a Date column to the NPI issues data source to use instead of the separate table. This way I didn't have to use the aggregated data functions and link the two tables. Then it worked!

• ###### 6. Re: Data values change by date

Well, I thought that had worked, but upon closer inspection it only worked for the first few values (since there are only a few records in the Dates column). Any further ideas would be really helpful!

Grayson Deal