Category Archives: Metrics

Converting seconds to time format

Hello there,

Today let’s talk about a quick tip: how to transform seconds to hh:mm or even hh:mm:ss.

You just have to:

  1. Create a new metric using your seconds metric and divide it by 86400.

Example of the formula would be: [metric with seconds] / 86400

2. Format the number to be like [h]:mm or [h]:mm:ss

 

That’s it. You will now see your seconds in a time format.

Hope it helps!

God bless you!

Using a “case” function multiple times

Hello there,

Today a quick tip about how use the case MicroStrategy function multiple times. To do that you just have to:

  1. Create a metric
  2. Add an expression like this:

Case(([metric] = 1),‘true’, ([metric] = 2), ‘true’, ‘false’)

Blue: The condition

Green: When the condition is true

False: When the condition is false

This way, you can add as many conditions as you need. Just replace the ‘false’ part with a new condition.

Hope it helps.

 

God bless you

Number format in MicroStrategy

Hello there!

My friend Diogo Martins, told me a simple but powerful custom formatting to numbers that can help you so much!

Here is the tip:

Think that you have a Profit metric with a number like this: 1.304.141

And you want to divide that by 1000.

You just have to:

  1. Format the value and go to Custom
  2. Add a format like: 0,

Capturar

And now you will get your number like: 1304

The trick is the comma (,) at the end.

If you add 1 comma you will dividing by 1.000

If you add 2 commas you will dividing by 1.000.000

And so on…

So, just add commas after the 0 (for number) and you will format your number in a much better way than dividing it in your metric.

Hope it helps!

God bless you!

Adaptive Metrics

Hello there!

Today let’s talk about how to create different aggregation formula for each fact table used in a fact in Microstrategy.

Let’s say that we need to create a SUM(value) for Fact A and a COUNT(value) for Fact B. When we get data from Fact A, Microstrategy must SUM, when in Fact B, Microstrategy must COUNT.

To do that you just have to:

1. Create a new Fact;

2. Create a new expression with:

ApplySimple(“Count #0”, FIELD1) 

Only use Fact B for source tables.

3. Create a new expression with:

ApplySimple(“Sum #0”, FIELD2)

Only use Fact A for source tables.

pic

Save your Fact as: Fact_Test

4. Create a new Metric with:

ApplyAgg(“#0”, Fact_Test)

pic

5. Update your Schema.

 

That’s it.

Hope it helps

 

God bless you!

How to show the parameters set in metrics

Hello there!

Today I’m going to teach you a quick tip that can help you see the parameters that were set in a metric.

The default behavior of MicroStrategy is the hide all parameters set for your metrics. it’s much better to see everything that you are using.

To do that you just have to:

1. Open Developer/Desktop and edit a metric

2. Go to Views -> Show Function Parameters

pic

Now you will see every parameter that you metric have.

pic

 

Hope it helps!

God bless you!

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!

 

 

 

Changing Metric Column Alias Name

Hello there!

Today let’s talk about changing the metric alias name in the SQL that MicroStrategy generates.

Sometimes you need to debug the query and you see something like this:

select name_pu as name, sum(salary) as #MAS01MQW from Fact_Table group by name_pu

If you have a lot of metric inside your report, it is very hard to know where is a specific metric in the query.

You can change that machine metric alias name to a human metric alias name using Metric Column Name.

To do that, you just have to:

1. Edit your metric;

2. Go to Tools;

3. Go to Advanced Settings;

4. Choose “Metric Column Option

pic2

Then, you just have to type a different name in the “Column Name used in table SQL creation“, like the image below:

pic1

Now your SQL is going to be something like this:

select name_pu as name, sum(salary) as Name_of_Metric from Fact_Table group by name_pu

That’s it.

Hope it helps!

God bless you!