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

# SuperStore Sample - Aggregating Data

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

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

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

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