6 Replies Latest reply on Mar 5, 2012 7:38 AM by Tim Hill

    How to calculate for rolling previous 30 days

    Tim Hill

      I'm working on a new set of charts where I need to total the measures for the last 30 days for each day.  What I mean by that is each day already has an amount for it that I'm pulling in from the database but I need to be able to calculate the sum of the previous 30 days for each day.  Then I'll want to display the data in a monthly line chart and eventually a weekly line chart, where the value for each week/month is the total for the last 30 days from the end of that week/month.  The table calculations work good for showing a monthly chart that has the sum for that month but that includes more or less days than 30 based on which month it is and to do the same thing for weeks would not show 30 days but instead just 7 days.  Does anyone have an idea about how to calculate this or know if I missed something in the table calculations?

        • 1. Re: How to calculate for rolling previous 30 days
          Tracy Rodgers

          Hi Tim,

           

          Would placing a relative date filter (showing Last 30 days) help to get the desired outcome?

           

          -Tracy

          • 2. Re: How to calculate for rolling previous 30 days
            Tim Hill

            That would only filter the chart down.  I need to show the 30 day sum of these values for any given day.  So if I'm displaying a monthly chart I need the sum of the last 30 days from Dec 31, Jan 31, Feb 28, etc. and if I'm displaying a weekly chart I need the sum of the last 30 days from the end of each week, Saturday in my case.  So filtering doesn't really give me what I need.  There should be a way to calculate a sum based on a date field but I can't see how to do it.

            • 3. Re: How to calculate for rolling previous 30 days
              Shawn Wallwork

              Tim it sounds like you're looking for a calculation that uses the PREVIOUS_VALUE( ) function. But without a sample workbook it's hard to tell how you'd implement it.

               

              --Shawn

              • 4. Re: How to calculate for rolling previous 30 days
                Tim Hill

                I have not used the PREVIOUS_VALUE function before so I will look into that.  I'm going to go ahead and attach a workbook now though because that is a good idea.  This workbook shows the problem that I'm facing pretty well.  The Unique Users are already aggregated to be UU from the last 30 days when I pull it from the database.  Churned Users, New Users, and Returning Users are all daily values.  I want to have those calculated for the last 30 days as well and then we can compare them to the UU values.  Once I hae the 30 day calculations it will also help me calculate things like the Retained Users and the percentages that these values are of UU.  A secondary problem that I haven't investigated yet because it doesn't matter without the 30 day totals is that I will want to display the weekly and monthly charts with these 30 day values from the last day of that period instead of the sum of the 30 day totals for that period.  I think I can get that working though once I have the 30 day totals figured out.

                • 5. Re: How to calculate for rolling previous 30 days
                  Andrew Watson

                  Try this calc:

                   

                  IF index() >= 30 THEN WINDOW_SUM(SUM([Churned Users]),-29,0) END

                   

                  This calculates the rolling sum of the previous 30 entries - note this doesn't necessarily equate to 30 days - if a day is missing from the dataset then it would sum more than 30 days worth.

                   

                  I've attached your workbook with a tabular and graphical rolling 30 days sheet.

                   

                  Andrew

                  1 of 1 people found this helpful
                  • 6. Re: How to calculate for rolling previous 30 days
                    Tim Hill

                    Thanks Andrew.  This is along the lines of the last thing I was trying and it mostly works.  The problem is that I don't want to display the chart using the exact date on a daily basis like that, at least that's not what my requirement is for this chart.  That may be a more correct way to display it than the weekly and monthly basis that I'm being asked to though and something I'll have to look into.  Either way, it does seem to do the calculation correctly if I can display the values for each day so thanks for your help.