3 Replies Latest reply on Aug 28, 2018 5:21 AM by Ken Flerlage

    SuperStore Sample - Aggregating Data

    Jay Khay

      Hola Amigos. I'm using the SuperStore Sample source, 'Order Date' and 'Sales'.  I'd like to take the monthly data for 2017 and group them into 3. Specifically, for 2017:

       

      From:

      To:

      GroupSales
      A (Jan - March 17)23,318
      B (April  - June17)6,572

       

      Then,

        I'd like to be able to use these groups in additional calculations. Say, something simple like Group A - Group B/ Group A

       

      Thanks!!

        • 1. Re: SuperStore Sample - Aggregating Data
          Ken Flerlage

          You could start out by creating a calculated field something like the following:

           

          Group

          IF MONTH([Order Date])>=1 AND MONTH([Order Date])<=3 THEN

              "A (Jan - Mar " + STR(YEAR([Order Date])) + ")"

          ELSEIF MONTH([Order Date])>=4 AND MONTH([Order Date])<=6 THEN

              "B (Apr - Jun " + STR(YEAR([Order Date])) + ")"

          ELSEIF MONTH([Order Date])>=1 AND MONTH([Order Date])<=9 THEN

              "C (Jul - Sep " + STR(YEAR([Order Date])) + ")"

          ELSE

              "D (Oct - Dec " + STR(YEAR([Order Date])) + ")"

          END

           

          This will give you each of the groups you're looking for.

           

          Doing the math between these values is a bit trickier and it kind of depends on how you want to use them. In some cases, use of LOOKUP will allow you to grab a prior sales amount, so that may work (again depending on your use case), but the most straightforward approach may be to create different measures for each group. For example, the following calcs will create separate measures for the A 2017 and B 2017 groups:

           

          Sales A 2017

          IF [Group]="A (Jan - Mar 2017)" THEN

              [Sales]

          END

           

          Sales B 2017

          IF [Group]="B (Apr - Jun 2017)" THEN

              [Sales]

          END

           

          Then you can create one more calculation to do the final math:

           

          Difference

          (SUM([Sales B 2017])-SUM([Sales A 2017]))/SUM([Sales A 2017])

           

          I've included a packaged workbook. If this answers your question, then please be so kind as to mark this as the "correct answer." This will allow us to close the thread and will also make it easier for others who may have similar questions in the future. Thanks!

          1 of 1 people found this helpful
          • 2. Re: SuperStore Sample - Aggregating Data
            Jay Khay

            Hi Ken,

              Thanks for your help, the answer is pretty much spot on. Just wondering, is there a way to create a line graph out of these groups and show the % over the total for the year?

            • 3. Re: SuperStore Sample - Aggregating Data
              Ken Flerlage

              Yep, you should be able to do something like this. On the row shelf, I've dragged over Sales, then right-clicked on it and chose to create a "Quick Table Calculation" of type, "Percent of Total"

              See attached.

              1 of 1 people found this helpful