Month over Month custom measures

[Sherwick]

I simply want to create a month over month measure but taking current COUNT OF X (where X is any existing measure I’ve already created) minus last month’s COUNT OF X and plot the difference over time. How do I do this? I have given up searching as it is a hit or miss through the forums. I have tried looking through the User Guide but am lost there as well. Help.

[rachel_johnson]

Hey @Sherwick,

In SQL, we are not able to nest aggregate functions within each other, which would be necessary here when counting a measure. There are two methods in Looker that can be used to count an aggregate - using table calculations within the explore or creating a derived table. The specifics of the use case can help determine with method is best!

[lloydtabb]

This gets a lot easier with the up and coming filtered measures, but for now the best way to do this is to filter on the months you want and pivot. For example, here are the past two complete months of user signup.

Then create a table calculation that calcuates the difference between the two columns.

You can do this by bonking the ‘calculate’ button.

And the result looks like this:

Hide the months column and visualize.

[lloydtabb]

By the way, if you want to compare specific months you can use custom filters for example, if you wanted to compare 2017-06 and 2016-06 you can create filter for the months for that timeframe.

[Sherwick]

Thanks - this helps a lot.

[Bruno_Benevides]

Hi Shrwick,

In that case you need two create two calculation fields.

One will be the previously month, and the other the division current month / previously month. (you have to hide the previously month from visualization).

1 - Creating the previously month:

a) Go to calculations;
b) user the formula offset(variable_name, 1).

2 - Create the MoM rate:

a) New calculation with current month / previously month.

3 - Go to edit visualisation, choose comparison and then choose “value change”