2 Replies Latest reply on Jul 23, 2012 2:32 PM by Jason Strimpel

    Finding Max and Min of Values Aggregated By Month

    Jason Strimpel

      Greetings,

       

      I have data about company sales with dates. There are many sales within each month. I need to find the max sum of sales over the past 12 months. For example, if I have three sales in June for $100, 10 sales in May for $110, and five sales in April for $90, I need to return $110 as the max and $90 for the min.

       

      When I aggregate, I either receive an error saying I cannot aggregate an already aggregated function, or I return the max and min sales figure within the month.

       

      Further, I would like to just show a bar chart that starts at the min value found as the minimum value on the chart and that ends at the max value found as the maximum value on the chart. Attached is a screenshot example done in Excel.

       

      Any help is appreciated.

        • 1. Re: Finding Max and Min of Values Aggregated By Month
          Tracy Rodgers

          Hi Jason,

           

          One way to get the min and max, create two calculated fields using the window_max and window_min functions. For example,

           

          window_max(sum(Sales))

           

          window_min(sum(Sales))

           

          Make sure that the month field is on the level of detail shelf. Right click on each calculation on the view and select Compute Using-->month. Then, create a dual axis with these two fields. Right click on one of the axes and select Synchronize Axes. Finally, right click and edit the axis so that it is fixed to start at the minimum value.

           

          Hope this helps a little bit!

           

          -Tracy

          • 2. Re: Finding Max and Min of Values Aggregated By Month
            Jason Strimpel

            That's amazing, thank you!

             

            The next step was to simulate a bar so I added a reference line from the max value to 0 with a color fill and a reference line from the min value to 0 with a white reference line. This gave a look of a filled bar.

             

            Last thing I am trying to do is add a mark on the scale  with the last month's sales. Any insight into how to do this? Ideally it would be a shape mark.