Adding filter in a Derived Metric

Hello there!

Today let’s talk about an excellent trick with derived metrics.

There is a way to insert a filter in your derived metric!

Let me show you an example:

1. Create a filter with Year = 2010 and save your filter as “2010“:

pic2

pic2

2. Create a blank report with Year and the metric Cost:

pic1

As you can see, I am fetching all years from database and displaying the Cost for each year.

2. Create a new derived metric:

pic2

3. Use a Sum formula, for example, like this:

Sum(Cost) {~ }

pic2

4. Insert <[name_of_the_filter]; @x; -> after the dimensionality of the metric. Where @x is the embedding method number:

In my case, I’m going to write: <[2010]; @2; -> 

pic2

In x you can use:

1 – Merge into new;

2– Merge report filter into metric;

3– Merge metric condition into report;

When you edit your metric and select Condition there is an Advanced… button where you can see those options.

You can also use + instead of the :

<[2010]; @3; +> – That means that you will “Remove related report filter elements”

<[2010]; @3;> – That means that you will not “Remove related report filter elements”

 

5. Now, edit your “New metric” and use the Fact Cost instead of the Metric Cost in the formula:

Was: pic2

Now: pic2

6. Save your metric;

7. Re-run your report:

pic2

As you can see, we have filtered only 2010 to the “New Metric“! So, you don’t need to create a lot of metrics with different filters to use in just 1 report, you can create a local filtered metric and reduce the amount of metrics created in your project…

Hope it helps!

God bless you!

 

 

 

10 Replies to “Adding filter in a Derived Metric”

  1. Hi, I know this article is a bit older, but nonetheless good 🙂 The only problem I have is that instead of getting what you get under 7., the lines that would return 0 for the filter are simply removed from the report. It’s as if the lines 2011 and 2012 were gone from your report, even though there are values for other metrics.

    I’ve used the following formula: Sum([myAttribute]){~}

    Instead of MSTR Desktop I’m adding the values in MSTR web, though.

    Cheers

    Philippe

    Like

      1. Do you have any idea why this could be? Is there some other setting I am missing?

        I tried setting all the options I could do “outer join”, but still nothing, the lines just disappear.

        Like

      2. Sadly the base attributes themselves are out of my reach, but maybe I can convince someone to help me out there 😉 But no matter the outcome, thanks a lot for your great help; much appreciated!

        Like

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