Today let’s talk about creating subtotal over attribute elements.
Let’s suppose I have 2 attributes and 1 metric in a report template: Year (in columns) , Category (in rows) and Profit(in columns). Let’s say that I need to calculate the growth of Profit among 2011 and 2012 for each category. How can I do that?
One thing that could be done is to create 2 metrics and insert a conditional “2011” for the first metric and “2012” for the second metric. Using this procedure I will have some problems to calculate the growth over different years. I would have to create another metrics with another filters for each different year.
That isn’t a good approach. Instead, you can create a subtotal that gets the first year and the last year automatically and then calculates that growth for me.
To do that you have to:
1. Go to Project Objects -> Subtotals
2. Create a new Subtotal with this formula:
Here I’m using the Last and First function to get the first year and the last year of my report. The trick here is to use the SortBy = Year. If you don’t use this SortBy, MicroStrategy won’t get the correct value for First and Last. The “@” indicates the place where Profit Metric will be inserted.
If you can’t see the functions parameters, just go to View ->and click on “Show Functions Parameters”, just like the image below:
3. Go to Profit metric and include this subtotal to the available subtotals:
4. Create a new report using these objects below:
5. Go to Data-> Subtotals and add check the Growth subtotal.
6. Now when you run your report, MicroStrategy will divide 2012/2011 just like we wanted.
Hope it helps.
God bless you.