11 Replies Latest reply on May 21, 2018 12:36 PM by Prateek Nagar

# Lookup First non null values and set it as the base value

Hi,

I have some data (see screenshot below) that displays average temperatures for each country. I would like to create a table that displays the difference for each year to base year, depending when the data first became available.

As you can see in the picture below, for some countries, the data becomes available at a later time.

I'm having trouble with a calculation that will look up the first non null value and then make that the base year to which all the following years will be compared to. I would like to take each year and subtract the base year from it to get the difference.

• ###### 1. Re: Lookup First non null values and set it as the base value

You can create a calc that uses the PREVIOUS_VALUE function.  It would run along the line and look at the current value of AVG(Ferenheit).  If it's null, set the calc to null. If it's not null, and if PREVIOUS_VALUE is null, then you have the first in the series.  Set it to the AVG(ferenheit) value.  If current value is not null, and if PREVIOUS_VALUE is also not null, then just grab PREVIOUS_VALUE (which would end up propagating the first value for the rest of the series.)

That's how it would work in theory.  The specific implementation ... I would have to play with it to make sure it's coded properly.

If you can't get this to work, upload a sample workbook and I'll show you.

• ###### 2. Re: Lookup First non null values and set it as the base value

Thank you Joe. I've actually attempted to use Previous_Value function before making this post but was not able to make it work. I've attached the workbook. Thank you for all your help.

• ###### 3. Re: Lookup First non null values and set it as the base value

Looking at the data and at Sheet 5, we might be able to get away without PREVIOUS_VALUE.

I made a FIXED LOD calc that gives us the first date of any non-null data for each country.  I added that value after the Country column on Sheet 5.  If this approach is valid you can grab the AVG(Ferenheit) where the YEAR(DT) is the same as the YEAR(First Date...).  Is it a safe assumption that the first recorded temp indicates the first year you have an AVG(Ferenheit)?  On the surface that makes perfect sense to me, but you might know something more about the data than I am aware of.

But I also wrote up a calc that uses PREVIOUS_VALUE.  See [First AVG F].  If you do WINDOW_MAX([First AVG F]), you'll have that first value, wherever it lands on the row,  Throw my calc onto TEXT and you'll see what it does.

See attached.

• ###### 4. Re: Lookup First non null values and set it as the base value

Thank you Joe. I will give this a try. Sorry for the late reply.

• ###### 5. Re: Lookup First non null values and set it as the base value

Hi Joe,

I've tried this formula. It does work for values that start at the beginning of the year range (1743) where it calculates to difference from base year. However it does not work for any other values where temperatures were not available in 1743. Was it the same way for you when you tried? Any suggestions?

• ###### 6. Re: Lookup First non null values and set it as the base value

Did you look at the example I uploaded?

I took what I wrote in that last reply and threw the [First AVG F] calc on TEXT.  I also created [Calculation2] that just compares year(Dt) to year(First Date...) and grabs the avg.  (It's a FIXED LOD that acts at the level of [Country].)

Both are displayed on Sheet 5 in the attached.

• ###### 7. Re: Lookup First non null values and set it as the base value

Thank you Joe. This worked great. I just need to subtract the First AVG F calc from the Avg temp to get my difference from base.

• ###### 8. Re: Lookup First non null values and set it as the base value

Hi

I am also struck with similar kind of issue where I need to find difference between metric values among weeks.

Its a weekly data from 1st week till 20th week.

I have nulls in my data and find the moving difference from larger week to the smaller week and if there is a null then lookup for the previous non-null value in the row and then do the subtraction.

I am attaching the sample tableau file for your reference.

Your help will be highly appreciated.

Thanks,

Prateek

• ###### 9. Re: Lookup First non null values and set it as the base value

Prateek -- I don't have time to get to this today, and the only people who are likely to see your post in this closed thread are the ones who previously participated in this thread.

• ###### 10. Re: Lookup First non null values and set it as the base value

Please take a look at my post as well.

Thanks....

• ###### 11. Re: Lookup First non null values and set it as the base value

Thanks for the update, I have created a new thread and posted my query there. ☺

Thanks,

Prateek