2 Replies Latest reply on Jul 13, 2016 11:10 AM by Yannick Klausener

    Sum by Day, Then Average by Month

    Aaron Rubinstein

      I have a dataset that has multiple records for each day, with a "Rig Count" value for each record. I am trying to create a line chart that shows the average Rig Count per month by Operator, where the average Rig Count would be calculated by taking the sum of the rig counts for each day, and then averaging those sums to get an average for the month.


      After searching the forums I think I figured out how to setup the calculation for my average:

      IF FIRST()=0 THEN WINDOW_AVG(SUM([Rig Count])) END


      I then set the "Compute Using" to Month, Day, Year of Report Date and drop the MDY pill on the Level of Detail shelf. This gets me an accurate result in a cross-tab view, however I can't seem to convert it to a line chart. Having MDY on the Level of Detail shelf breaks the line into individual marks, but if I remove that pill the table calc no longer works.


      I have attached a sample workbook.


      Any help would be greatly appreciated.

        • 1. Re: Sum by Day, Then Average by Month
          Mark Holtz



          You were using the right function--WINDOW_AVG(SUM(x))... you just need to get the partitioning and compute along fields set correctly.  With any table calculation, you have to define these, and it's still a point of confusion for me often times.


          In your case, you needed to be able to access the Month value of the date field and the Day value of the date field so that you could partition by Month and compute along Day. So, in the attached workbook, you'll see that I just created a second field that gives the Month value (truncating off the day, so 1/17/2012 and 1/5/2012 become 1/1/2012).


          Then, I used the window_sum function and set the table calculation to calculate using Report Date (the description then states that results are compute "along Day of Report Date for each Month of Month--in other words, average the day totals, get a new average for each month).


          One more thing--the FIRST () function is also a table calculation, but not one you'd want to use in this case. Essentially FIRST() or LAST() allow you to pick a single row of the partition, but you don't actually want that here because you want to get the value for each item in the partition and aggregate them...


          Anyways, hope this helps!

          • 2. Re: Sum by Day, Then Average by Month
            Yannick Klausener

            Aaron Rubinstein Any updates on this topic? I've been trying to do the same and Mark Holtz solution won't work as I need this calculation to be done in a row level.

            In short, what I've been trying to accomplish is: using a sales data, calculate the number of days where sales effectively happened in a sequential form, with start and end on each month.

            • Example:
              • January: Sales on the third, sixth and twelfth days, would be considered as 1°,2° and 3° day respectively. Same for every month, ignoring the dates where no sales happened.


            With those available, calculate the average sales for each of those sequential days.