4 Replies Latest reply on Apr 22, 2017 6:03 PM by Zhouyi Zhang

# Previous Non Null Values

I am new to Tableau and would greatly appreciate if someone could help me with this. I have a report that shows foreign exchange conversion rates. There are some days that some currencies are blank. On the days that these conversion rates are blank, I want the previous non null rates to be picked up. I used the previous value formula as below:

if isnull(LOOKUP(sum( [Ukurs] ),0 )) then PREVIOUS_VALUE (sum( [Ukurs] )) else sum( [Ukurs] ) end

It works fine for all other days but on Mondays if this rate is blank, then it looks for the previous day which is Sunday (the rates are not populated for Saturdays and Sundays) and since it finds it blank, it returns blank. I would want to be able to pick the first previous non null rate. Attached is the picture of the report.

• ###### 1. Re: Previous Non Null Values

Hi

Without seeing your date dimensions my best guess would be to add an If statement to check the day of the week

Something like >  IF DATENAME('weekday',[Order Date])='Monday' then (here put in your statement to look back 2-days value)

Else (here put in the statement you use above) End

Let me know if this helps

Jim

• ###### 2. Re: Previous Non Null Values

Yes Jim, One more point to add here..If there is a blank in the 5 days during the week then you need to include for that too in formula.

Thanks

Deepak

• ###### 3. Re: Previous Non Null Values

Right you are

Jim

• ###### 4. Re: Previous Non Null Values

Hi, Sunita

your formula should work as you expected. I just create a sample workbook and put your formula(Red line) you provided and show the result as below.

All the missing rate in blue line shows in the red line from its previous value.

cu

I guess may be the way of data is different. If you could provide a make up workbook with just couple of records to indicate the issue would be more helpful to investigate.

ZZ