9 Replies Latest reply on Feb 3, 2012 12:59 AM by Fabio Annovazzi

# Subtracting value on day N-7 from day N value (with days missing)

Hi,

New to Tableau, love it, but suffering a bit

I have database with the number of fans of facebook fan pages.

Theoretically the file is updated every day, but often days are missing. The file has the number of fans on every given day. I want to calculate the weekly increase in fans. I need to find a way not to perform the calculation when I am missing the value of seven days before.

I tried using the table calculation with -7 as a parameter, but it simply subtracts the "seventh" previous value (and that can be the value of 10 days before, if 3 days are missing). Tried with the tableau lookup but could not figure out how to tell the function not simply to move seven cells to the left, but to catch the data corresponding to the seven days before.

In excel I have concatenated the ID of the page and the N-7 date value and I use a vlookup function to pull the right data. If the vlookup does not find the value, the cell stays blank.

In other words, is there any way of subtracting from the value of a metric in day N, the value of that same metric in the day N-X, even if in the db some of the values between and X are missing?

My data looks like this

Facebook page              Date        Number of fans

Coca-cola                       13/1/2012     30.000.000

Coca-cola                       14/1/2012     30.030.000

.....

.....

Pepsi                               13/1/2012     10.000.000

Pepsi                               14/1/2012     10.000.000

....

....

Thanks so much

fabio

• ###### 1. Re: Subtracting value on day N-7 from day N value (with days missing)

I think we need to see the raw data and what you expect as a result - can you post the data, or at least a mock up of it. You are right that lookup or previous_value will not perform the way you are using them.

• ###### 2. Re: Subtracting value on day N-7 from day N value (with days missing)

Hi Alex,

Here is the excel I work with. I left the formula only on the first row, to save file space.

The data I have is

Facebook Page ID, Date, Number of Fans.

Other fields are calculated.

I calculate the "day number" of the date (in fact how many days since the 1/1/2010) subtract 5 days from that value, and concatenate it with the page ID. Basically my "concatenate 5 days before" is what I use to catch the "number of fans" data of that same page five days before with a vlookup. If it returns error, it means that that day I have no data, and I black the row. Otherwise I subtract the present value of fans from the value of 5 days before.

Thanks again

fabio

• ###### 3. Re: Subtracting value on day N-7 from day N value (with days missing)

Okay, look at the attached, which isn't the answer but is a start. I've split the ID and date and made it a proper date. I believe you are looking for a week on week change, but this data set doesn't support that because it's just a week (hence why you changed it to a 5 day lookup). I think the route we need to take is using a datetrunc('week',[Your Date]). However, there are two problems to solve:

• Often there are a number of readings for a single week. Which one to choose? Or, an average for all during that week, or a min/max?
• What to do with missing data? Interpolating is tricky to say the least.

Let me ask this: why do you need to interpolate for a missing value. Would instead the style of charts I've made a start on (with %difference from prior reading) give you the information you need?

• ###### 4. Re: Subtracting value on day N-7 from day N value (with days missing)

Hi Alex,

Thanks for this. I will open it immediately.

In fact I do not interpolate. I leave the data blank (ie not values available for missing dates). I guess you could interpolate, but what is important is not to get it wrong by subtracting from the wrong date. The missing values were due to bugs in the software that captures these values, and hopefully it should happen less often in the future.

Very briefly what I am after -  every day Facebook publishes a number ("people talking about") that is the total of how many times fans have interacted with the page in the previous seven days. This comments, likes and number of new fans. I need to "purge" the number from the total of new fans in order to get a number that shows how much the page is "engaging" its fan base.

In fact I go back 5 days and not 7 because often Facebook's algorithm that calculates the talking about values "gets stuck" and republishes the same value for two or three days in a row, with the result that new fans becomes greater than the number talking about (so I would have a negative engaged number). By going back a little less I sometimes underestimate the number of new fans, but the error is bearable.

As for the first question every day I get the value of 5 days before, so for each page I have 5 values per week (with which I build the daily fan growth of the page for that week)

thanks a lot

fabio

• ###### 5. Re: Subtracting value on day N-7 from day N value (with days missing)

Hi Alex,

About the % difference from prior reading, my problem is that if I have 7 days of no date, the % difference from prior reading will be today vs seven days ago, while normally it would be today vs yesterday. Is there any way around this?

thanks again

fabio

• ###### 6. Re: Subtracting value on day N-7 from day N value (with days missing)

Can you provide data that matches the case you're talking about - the current data set doesn't - a mock-up of the expected result would also help. I'm still not getting it. Sorry.

• ###### 7. Re: Subtracting value on day N-7 from day N value (with days missing)

Hi Alex,

You are 100% right. I have cleaned up the data to make it understandable. Now it has the data for two pages (A and B). On the right you have the data I get from Facebook. On the center the calculations I do on Excel (concatenate and all) that I would like to eliminate, and on the right the output (increase of fans over the last 5 days, per page) that I would like to calculate directly on Tableau. As you notice on some day, when there is no corresponding value for a given page five days before, the data is simply blank, while with my present calculation in Tableau, it just goes back until it finds the 5th preceding value, and subtracts that from the value in that day. If there are a lot of values missing, you get a "jump" in number of fans in that day that is not true. I prefer to leave the value "new fans since 5 days before" blank when I do not have the value of 5 days before.

Thanks again

fabio

• ###### 8. Re: Subtracting value on day N-7 from day N value (with days missing)

Okay, think this is what you want. I have faked a vlookup in Tableau. As the maximum number of rows you ever need to go back is 5 (assuming a day can never have two entries for a single page) it's easy enough to go back and see if you can find a row with a date 5 days ago.

if attr([Date])-5=lookup(attr([Date]),-1) then lookup(attr([Fans]),-1)

elseif attr([Date])-5=lookup(attr([Date]),-2) then lookup(attr([Fans]),-2)

elseif attr([Date])-5=lookup(attr([Date]),-3) then lookup(attr([Fans]),-3)

elseif attr([Date])-5=lookup(attr([Date]),-4) then lookup(attr([Fans]),-4)

elseif attr([Date])-5=lookup(attr([Date]),-5) then lookup(attr([Fans]),-5)

end

When used, the lookup partitioning is set to page ID (you must always have this on a shelf somewhere) and the order is set to date ascending. There could be other ways of doing this that others may have an idea about as well.

• ###### 9. Re: Subtracting value on day N-7 from day N value (with days missing)

works like a charm.

Thanks!!!

best

fabio