Custom Subtotal over attribute elements

Hello there!

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

pic1

2. Create a new Subtotal with this formula:

pic2

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:

pic8

3. Go to Profit metric and include this subtotal to the available subtotals:

pic3

4. Create a new report using these objects below:

pic4

5. Go to Data-> Subtotals and add check the Growth subtotal.

pic5

6. Now when you run your report, MicroStrategy will divide 2012/2011 just like we wanted.

pic6

 

That’s it.

Hope it helps.

 

God bless you.

2 thoughts on “Custom Subtotal over attribute elements

  1. Preeti

    Hi,
    I have a similar kind or requirement.
    With year,when I am adding the month attribute to column then the growth value is not coming correctly.By keeping only year growth value is coming correctly,but not after the adding the month attribute to column.

    Like

    Reply
    1. Felipe Vilela Post author

      hi Preeti. First, check the dimentionality of your Metric to make sure that it can calculate at a Month level. also, check the sql to investigate how mstr is calculating your growth.

      Like

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s