8 Replies Latest reply on Feb 15, 2018 7:50 AM by mark schukas

    calculate six month average for first six months of 2016

    mark schukas

      I would like to:  calculate the six month average for first six months of 2016 - let's say for sales in the SuperStore data.

       

      (this sounds simple enough, but after I restrict my year and months, I get the Error:  "can't mix aggregate with if statement..."

       

      ?

       

      thank you.

        • 1. Re: calculate six month average for first six months of 2016
          Michael Ye

          Mark,

           

          Please see attachment (V10.2) and screen shot.

           

          Build a calculated field: Avg of first 6 months and put in detail, then you can add reference line:

           

          WINDOW_SUM(SUM(IF MONTH([Order Date])=1 THEN [Sales]

          ELSEIF MONTH([Order Date])=2 THEN [Sales]

          ELSEIF MONTH([Order Date])=3 THEN [Sales]

          ELSEIF MONTH([Order Date])=4 THEN [Sales]

          ELSEIF MONTH([Order Date])=5 THEN [Sales]

          ELSEIF MONTH([Order Date])=6 THEN [Sales]

          END))/6

           

          The avg of 12 months is 61,162.

           

          The avg of first 6  months is 42,940.

           

          Hope it helps.

           

          Michael Ye

          3 of 3 people found this helpful
          • 2. Re: calculate six month average for first six months of 2016
            Justin Larson

            There are a number of ways to answer this question, dependent on nuance of how you want this figure to be used or shown on a report. You'll probably need to be more specific to get a reasonably useful answer, aided largely by a sample workbook that gets as close as you can, or shows where you ran into the error.

             

            For example, when you say average sales, do you mean the average value for each row? Average monthly sales? Do you need to just show this figure on the canvas by itself? Do you need to compare this number to other measures?

             

            Based on answers to each of these, you may get different approaches with varying combinations of layout, calculation, and filter configurations.

            1 of 1 people found this helpful
            • 3. Re: calculate six month average for first six months of 2016
              mark schukas

              thank you.

               

              at the moment, I just need to create the 6 month average (Sales) and show on a Worksheet

               

              (e.g., it's used as a comparison:  this year we sold $100 for January last year's monthly average was $90 - business rule is to compare to first 6 month's average for 2016)

               

               

              thanks...

              • 4. Re: calculate six month average for first six months of 2016
                mark schukas

                thank you.

                 

                seems to work OK for single Measure (Sales).

                 

                but if I need to use a Calc. Field (e.g., Ratio:  Sales/Profit), I get the error:  "can't mix aggregate...with an If statement"

                 

                ?

                 

                thank you.

                • 5. Re: calculate six month average for first six months of 2016
                  Justin Larson

                  So, you can do a calculation to evaluate if the date is in first six months of 2016. If so, return sales, of not, null. Then wrap that in your aggregate. As in:

                   

                  AVG(

                  IF YEAR([Order Date]) = 2016

                      AND MONTH([Order Date]) <= 6

                      THEN [Sales]

                      END

                  )

                  1 of 1 people found this helpful
                  • 6. Re: calculate six month average for first six months of 2016
                    Michael Ye

                    We can do just like the following:

                     

                    When Sales in a month are bigger than the first 6 months Avg Sales, it is green, otherwise, red.

                     

                    Thanks,

                     

                    Michael Ye

                     

                    3 of 3 people found this helpful
                    • 7. Re: calculate six month average for first six months of 2016
                      Michael Ye

                      Mark,

                       

                      Please remember Profit/Sales is a row level calculation. It's nonsense. Because when it moves to column or row, it will aggregate to SUM(profit/sales) or

                      AVG(profit/sales), etc, They are no significance. Only Sum(Profit)/Sum(Sales) represents the profit ratio. When it moves to the column or row, It never aggregate again, That is significant.

                       

                      If you want to compare the profit ratio, you can modify the calculation:

                       

                      WINDOW_SUM((IF ATTR(MONTH([Order Date]))=1 THEN SUM(Profit)/SUM([Sales])

                      ELSEIF ATTR(MONTH([Order Date]))=2 THEN SUM(Profit)/SUM([Sales])

                      ELSEIF ATTR(MONTH([Order Date]))=3 THEN SUM(Profit)/SUM([Sales])

                      ELSEIF ATTR(MONTH([Order Date]))=4 THEN SUM(Profit)/SUM([Sales])

                      ELSEIF ATTR(MONTH([Order Date]))=5 THEN SUM(Profit)/SUM([Sales])

                      ELSEIF ATTR(MONTH([Order Date]))=6 THEN SUM(Profit)/SUM([Sales])

                      END))/6

                       

                      You need to add ATTR() to change non-aggregate calculation(such as MONTH([Order Date]) to aggregate calculation (such as SUM(Profit)/SUM([Sales]).

                       

                      Here, the Avg Profit ratio of first 6 month: (0.1613+0.0795+0.2506+0.0256+0.1433+0.1552)/6=0.1359, so it is correct.

                       

                      Hope it helps.

                       

                      Michael Ye

                      3 of 3 people found this helpful
                      • 8. Re: calculate six month average for first six months of 2016
                        mark schukas

                        thank you...

                         

                        closer...

                         

                        works as you show above (good

                         

                        I would now like to generate the First six month AVG Profit and show as only on cell (.1359)

                        (no months on Column)

                         

                        make sense?

                         

                        thanks...