Category Archives: Functions

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

OLAP Functions: Lag and Lead

Hello there!

Today let’s talk about two cool OLAP Functions that can help you a lot: Lag and Lead.

Let’s think that you need to divide current month / last month. Those functions can help you do that. So…

1- Create a new Metric using the Function Lag using Cost like this:

pic1

Hit Next > button.

pic1

Where the ValueList is the Cost metric, Offset is how many previous data that we want to display, so it is the relative position,  and DefaultValue is the value that you want to show when there is no data for that specific row.

Hit Next > button.

pic1

In that case, we don’t want to Break-by any attribute.

Hit Next > button.

pic1

We need to sort by Year and Month to display the correct relative data.

Hit Finish button.

The formula must be something like this: Lag<SortBy= (Year, Month) >(Cost; 1,0; 1,0)

Then, save your metric and name it as Cost Last Month.

2- Create a report with Year, MonthCost and Cost Last Month;

pic1

As you can see, we have created an Offset of 1, and now, we can divide current month / last month.

3- Create a Derived Metric using this formula: (Cost / [Cost Last Month])

pic1

Format your new metric with Fixed with 2 decimals and you will have something like this:

pic1

If you use the Lead function, it will display the next value, like this:

pic1

That’s it. Lag is for previous relative data and Lead is for the next relative value.

 

Hope it helps!

 

God bless you!