
1. Re: Subtracting value on day N7 from day N value (with days missing)
Alex Kerin Jan 31, 2012 2:31 PM (in response to Fabio Annovazzi)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 N7 from day N value (with days missing)
Fabio Annovazzi Feb 1, 2012 12:27 AM (in response to Alex Kerin)Hi Alex,
thanks for your reply.
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

data for tableau.xlsx 3.4 MB


3. Re: Subtracting value on day N7 from day N value (with days missing)
Alex Kerin Feb 1, 2012 5:52 AM (in response to Fabio Annovazzi)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?

fans.twbx.zip 125.1 KB

4. Re: Subtracting value on day N7 from day N value (with days missing)
Fabio Annovazzi Feb 1, 2012 8:01 AM (in response to Alex Kerin)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 N7 from day N value (with days missing)
Fabio Annovazzi Feb 1, 2012 8:22 AM (in response to Alex Kerin)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 N7 from day N value (with days missing)
Alex Kerin Feb 1, 2012 4:18 PM (in response to Fabio Annovazzi)Can you provide data that matches the case you're talking about  the current data set doesn't  a mockup of the expected result would also help. I'm still not getting it. Sorry.

7. Re: Subtracting value on day N7 from day N value (with days missing)
Fabio Annovazzi Feb 2, 2012 1:27 AM (in response to Alex Kerin)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

data for tableau.xlsx 22.6 KB


8. Re: Subtracting value on day N7 from day N value (with days missing)
Alex Kerin Feb 2, 2012 1:03 PM (in response to Fabio Annovazzi)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.

fans 2.twbx.zip 41.0 KB


9. Re: Subtracting value on day N7 from day N value (with days missing)
Fabio Annovazzi Feb 3, 2012 12:59 AM (in response to Alex Kerin)works like a charm.
Thanks!!!
best
fabio