6 Replies Latest reply on May 13, 2016 1:07 PM by pooja.gandhi

# Grabbing Last Date if Value of Current Date is Null

Hello,

I am new to Tableau and am having difficulty with what I imagine is an easy question.

I have a series of economic data on countries, and various countries release their data at different times.  I would like to make a chart that automatically grabs the value of the series (call it GDP) on the most recent release, and lists the "as of" date next to it.

The data look like this:

CountryDateGDP
USQ3 20152%
USQ4 20151%
USQ1 2016null
JapanQ3 20150.3%
JapanQ4 2015-1.2%
JapanQ1 20160.7%

And the output would be a barchart that says:

US: Q4 2015: 1%

Japan: Q1 2016: 0.7%.

If I were writing it in R, the code would be:

group_by(Country) %>%

select (Country, Date, GDP) %>%

filter(ifelse(max(Date) == null), lag(Date,1), Date)

Thank you!

J

• ###### 1. Re: Grabbing Last Date if Value of Current Date is Null

Hey Jonathan,

Have you looked into PREVIOUS_VALUE or LOOKUP?

Alternatively, looking at your R calc, it looks like you are grouping everything by country, so you could use LoD calculations:

MaxDate:

{FIXED [Country] : MAX(IF NOT ISNULL([GDP]) THEN [Date] END)}

IF [Date] = [MaxDate] THEN [GDP] END

- Derrick

• ###### 2. Re: Grabbing Last Date if Value of Current Date is Null

Hi Jonathan!

I see the dates as strings and hence parsed them using DATEPARSE. If your dates in the db are already of date datatype, you can skip this step.

Convert the strings to date: DATEPARSE('QQQ yyyy', [Date]) - named it 'Actual Date'

Then a fixed level of detail (LOD) calc should help get the rest of your result:

if [Actual Date] = { fixed [Country] : max(if not isnull([GDP]) then [Actual Date] end )} then [GDP] end

Hope this helps!

1 of 1 people found this helpful
• ###### 3. Re: Grabbing Last Date if Value of Current Date is Null

Thank you both! It works!

Derrick - thats exactly what I was looking for.  The secret sauce was using the "Fixed" operator which is new to me.

Pooja - my data was not precisely in the form I typed it in so everything was read as a date.  But good to know about the Dateparse function.

Out of curiosity, whats the logic behind when avoiding the dreaded "All fields must be aggregated or constant" error?  I had previously tried

if isnull([Date]) then lookup([GDP],1) else [GDP] END with the intention of merging it with a "last", but got nowhere.

• ###### 4. Re: Grabbing Last Date if Value of Current Date is Null

When you aggregate fields, you can't combine them with unaggregated field.

There are a couple of methods to achieve this, depending on what you need.

Sometimes, I move the logic inside of the aggregation, for example:

COUNTD(IF [Xyz] = 'Yes' THEN [Lmnop] END)

Sometimes, it makes sense to use a LoD calculation instead, like we did above.

• ###### 5. Re: Grabbing Last Date if Value of Current Date is Null

All fields in a calculated field must be either be aggregated or dis-aggregated. In your formula above, isnull(date) is dis-aggregated. You could wrap the date under ATTR() to satisfy the error. Also, when using a table calculation like LOOKUP(), the fields need to be an aggregate. So LOOKUP(SUM([GDP]),0) and the final measure in the else statement must also be SUM([GDP]) to avoid the error altogether. Hope that helps!

• ###### 6. Re: Grabbing Last Date if Value of Current Date is Null

Just a minute of difference, haha! Good one, Derrick!