Tag Archives: functions

OLAP Metric Functions

Hello there!

I’ve created a website for Microstrategy Online Courses.

Now I’m recording an URL API course (Portuguese) and an Olap Metric Functions course (Portuguese). If you are interested in Microstrategy online courses, that site can help you out!

I’m going to record in Portuguese and in English.

Hope you enjoy it.

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!