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:


Hit Next > button.


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.


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

Hit Next > button.


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;


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])


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


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


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


Hope it helps!


God bless you!

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.