6 Replies Latest reply on Nov 21, 2011 4:37 PM by Edward Kikendall

    Sum by day, then average of sums by month

    Brent Holm

      I have multiple records per day that have a count field to be summed. Then I need to average these sums across a month. How do I go about this?

        • 1. Re: Sum by day, then average of sums by month
          Joe Mako

          Sounds like a custom table calculation is what you are looking for. The specifics depend on what else you want on the worksheet, and the interactions you want to allow for.

           

          something like

           

           

          WINDOW_AVG(SUM([count]))


           

          and then once that pill is placed on the sheet, you will want the compute using set to your "day" field.

           

          That will likely not produce exactly what you are looking for.

           

          If you can provide a sample packaged workbook that represents what you have so far, more details to fit your situation can be provided.

          • 2. Re: Sum by day, then average of sums by month
            Edward Kikendall

            Hello, I am having an issue similar to this issue.

             

            I have a created a custom field, for budgeted amounts throughout the year.  The formula works great, but when I add it to a new sheet, it multiplies each budgeted month amount by the number of days (Since it is summing the amounts).  I can change the format of the pill from a Sum to an average to get the correct amount, but when I do that, the Grand Totals in the report do not work. I can also change it to an attribute, but if I do that then the subtotals do not work.  Any suggestions on how I can fix this issue?

             

            Also, is there an easy way to modify the calculated field to include a IF THEN function to say, if it is not US or NONUS, then another criteria?  I want to make sure I can have a consolidated budgeted amount for US and NONUS together.  For example the month of 7 would be $1,457,404.61 (US amount of $972,693.47 plus NONUS amount of $484,711.14)

             

            Thank you for your help with this.

             

            IF [Area]="US" THEN

              CASE MONTH([Date])

              WHEN 7 THEN 972693.47

              WHEN 8 THEN 1059372.68

              WHEN 9 THEN 1219730.98

              WHEN 10 THEN 1274806.68

              WHEN 11 THEN 2188800.11

              WHEN 12 THEN 3472571.35

              WHEN 1 THEN 1242124.06

              WHEN 2 THEN 1344194.67

              WHEN 3 THEN 1932321.79

              WHEN 4 THEN 1656733.56

              WHEN 5 THEN 2729166.83

              WHEN 6 THEN 1945827.43

              END

            ELSEIF [Area]="NONUS" THEN

              CASE MONTH([Date])

              WHEN 7 THEN 484711.14

              WHEN 8 THEN 536135.74

              WHEN 9 THEN 532968.30 

              WHEN 10 THEN 560851.36

              WHEN 11 THEN 1593857.72 

              WHEN 12 THEN 4490484.26

              WHEN 1 THEN 986321.71

              WHEN 2 THEN 629124.21

              WHEN 3 THEN 612794.51

              WHEN 4 THEN 701015.31

              WHEN 5 THEN 755046.26

              WHEN 6 THEN 642078.95

              END

            END

            • 3. Re: Sum by day, then average of sums by month
              Joe Mako

              If you can provide a sample packaged workbook that represents your situation, a solution can be demonstrated. I believe what you are looking for is a formula like:

               

               

              IF FIRST()==0 THEN
              
               WINDOW_SUM(AVG([calc field])
              END
              


               

              with the field Date duplicated and placed on the level of Detail shelf, and the compute using for this table calc set to use the Date (copy).

              • 4. Re: Sum by day, then average of sums by month
                Edward Kikendall

                Joe,

                 

                Here is the data set that I am looking at.  As you can see, I used a calculated field for the "Average Budget" so that it was not taking the monthly budget and multiplying it by the days in the month.  The only issue on how this is set-up is that the Grand Total's for the row and column are not correct, since it is taking the average total for the 12 months.

                 

                The other question I was looking at was on Area that I have defined.  I have US and NONUS.  If I want to do a budget amount on a consolidated amount for the US and NONUS together, what would be the best way to do that in a calculation, since my current calculation breaks out by Area. Thank you for your help on this.

                • 5. Re: Sum by day, then average of sums by month
                  Joe Mako

                  Can you please provide a (.twbx) packaged workbook? I cannot open a .twb without data.

                  • 6. Re: Sum by day, then average of sums by month
                    Edward Kikendall

                    Sorry about that.  This should help you out.