5 Replies Latest reply on May 29, 2012 7:41 AM by Fabio Annovazzi

# Filtering out less recent data

Hi,

I am trying to analyze the new fans per week data that Facebook publishes daily for each fan page.

I calculate the ration of new fans to the total fans of the page. Since pages belong to different industries, I calculate the ratio at the industry level.

What I wanted to do was to find a way to calculate this ratio using only the data of the 30 most recent days, and not all the data since the beginning. This must be easy, and I tried with window_sum(sum[new linkes], last()-30, last()) but I either gor error or got the same result I get averaging all the data.

I am having a hard time understanding how to use table calculation properly. How should I do it? Is there any documentation that explains in detail the "tricks" needed to use "custom" table calculations?

Thanks

Fabio

• ###### 1. Re: Filtering out less recent data

Fabio, I know this is going to look a little odd but it's the easiest way to do this. Create a calculated field with this formula:

Then place it on the filter shelf and filter for 'True'. This will give you what you're looking for. Using DATEADD() returns a date, whereas DATEDIFF() returns a date part. So to subtract 30 days from 'now', it's easiest to add a negative.

--Shawn

1 of 1 people found this helpful
• ###### 2. Re: Filtering out less recent data

I was playing around with this a bit more and discovered a few interesting things:

1. If you use [Date]>=DATEADD('day',-30,NOW()  ) you'll get the past 30 days including today
2. If you use [Date]>DATEADD('day',-30,NOW()  ) without the '=' you get the same results
3. If you use [Date]>=DATEADD('day',-30,TODAY()  ) you'll get the past 30 days excluding today
4. If you use [Date]>DATEADD('day',-30,TODAY()  ) you'll get the past 30 days including today (or only the past 29 day excluding today, whichever way you want to look at it.)

I find it interesting that NOW() isn't effected by adding the '=' sign, whereas TODAY() is. I guess the message is, be careful and verify the results.

--Shawn

1 of 1 people found this helpful
• ###### 3. Re: Filtering out less recent data

Thanks Shawn,

I like the approach very much. Is there any way to make it "generic" - I mean not dependent on "today", but just on the 30 most recent days (assume the DB for some reason is not updated for a week, using "today" would end up giving me the last 23 days.

best

fabio

• ###### 4. Re: Filtering out less recent data

You could use MAX([DATE]). This would return the most recent data minus 30 days.

--Shawn

1 of 1 people found this helpful
• ###### 5. Re: Filtering out less recent data

Hi Shawn,

me again.

I tried with [Date])>DATEADD('day',-30,max([Date])) but it does not work because it give error of mixing aggregate and non aggregate arguments. I tried with attr([Date])>DATEADD('day',-30,max([Date])) and it does not work because it does not go into the filter shelf.

Thanks again

fabio