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

    Filtering out less recent data

    Fabio Annovazzi

      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
          Shawn Wallwork

          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:

           

          [Date]>DATEADD('day',-30,NOW()  )

           

          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
            Shawn Wallwork

            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
              Fabio Annovazzi

              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
                Shawn Wallwork

                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
                  Fabio Annovazzi

                  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