Help Calculating Period-over-Period Growth

[#1]

I need to create dashboards that show period-over-period growth based on point of sale data. The periods may be quarterly, monthly, or weekly. Here is an example output of quarterly growth:

Quarter	Sales	PriorQuarterSales	Growth%
2008-Q4	$1,000,000	-	-
2009-Q1	$1,200,000	$1,000,000	20.00%
2009-Q2	$1,300,000	$1,200,000	8.33%
2009-Q4	$1,400,000	$1,300,000	7.69%
2010-Q1	$1,500,000	$1,400,000	7.14%

To that end, my FiscalCalendar table includes YearQtr, YearMonth, YearWeek AND PriorYearQtr, PriorYearMonth, PriorYearWeek fields. Can I use set analysis or some other method to achieve the above?

Does someone have an example of this they can share?

Thanks in advance for your help!

[johnw · Accepted solution]

You can't do it entirely with set analysis, because a set is only evaluated once for the whole chart, not once per row.

You can do it with a date island and IF statements, but that has performance problems and some user interface issues.

My opinion is that the best solution is to handle it entirely with data. The attached example isn't exactly what you want - it is comparing a quarter this year to the same quarter last year. But it demonstrates one approach that makes do with only a single calendar. Hopefully it is obvious how to modify the data to connect to the previous quarter instead of the previous year.

To report Growth%, you won't be able to just use a pivot table like I do in the example. You'll need separate expressions for Sales, PriorQuarterSales and Growth%. Something like this (or you could replace the literals with flags):

Sales = sum({<QuarterType={'Current'}>} Sales)
PriorQuarterSales = sum({<QuarterType={'Prior'}>} Sales)
Growth% = Sales/PriorQuarterSales-1

Another data approach adds a new calendar, but not as a date island. That'll be my next post.

[johnw]

So here's an example of the new calendar approach. Again, not exactly your case. In this case, we're linking a specific month to that month and the previous two months to get a rolling quarter. Again, it should be a simple matter to adapt it to link this quarter to both this quarter and the current quarter, but using different flags. Then you'd write your expressions in a way that closely parallels what we did in the last example:

Sales = sum({<CurrentQuarterFlag={1}>} Sales)
PriorQuarterSales = sum({<PriorQuarterFlag={1}>} Sales)
Growth% = Sales/PriorQuarterSales-1

The difference here is that our chart doing the comparison can't use Quarter as a dimension, it must use AsOfQuarter. That might seem like additional confusion and more trouble, but it can make everything ELSE easier. With the previous approach, if you selected a specific quarter and had another chart with sum(Sales) in it, you'd actually get sales from the selected quarter PLUS the prior quarter. We don't have that problem with the approach in this post, because the Quarter field continues to map to exactly the same data. So when you want your dates to behave normally, use your normal calendar. When you want your dates to map to other dates based on selected flags or literals, use the AsOf calendar.

I think my general preference would be for the approach in THIS post, but I haven't thought about it very carefully, and it probably depends on the application.

If you need help adapting either solution to get the previous quarter, let me know.

Oh, and this example also demonstrates the date island approach. That's not my recommendation.

[#4]

John, Thank you!!! These examples are fantastic!

[#5]

Okay. The data is populating correctly, but it's still not right. Each result is appearing on a separate line & I cannot use this to calculate growth. What is wrong with this example (attached)?

[johnw]

If I understand what you're after, the problem is that you're including YearQtr as a dimension. Only AsOfYearQtr should be a dimension. That compresses your two rows to one, allowing you to calculate quarter to quarter growth in a third expression.

It also looks like you have your quarters backwards, so you're showing negative growth.

See attached for fixed version.

[#7]

Thank you John! It works perfectly in my real-world case!