Skip to main content

I have a metric that has two dimensions that have pasted values from another source.  What I would like to do is return the last month that there is a value > 0 for a specific row in the metric.  In Excel terms, this would be done with a MAXIF function where I would return the max month with the condition that “Net Installs” is > 0.  From the screenshot below I want to return the month dimension value of Dec 24

Can this be accomplished using just the BY modifier and the MAX aggregation method?  Any insights are very much appreciated.  Thank you in advance. 

 

 

Hello Kevin,

You can try to use this formula:
if('Your Metric'>0,Month)[remove lastnonblank : Month]

The first part of the formula return the Month dimension item for all numbers above 0

The second part of the formula will remove the Month dimension and only keep the “max” value being the last Month.

Let me know if it solve your issue!

This is excellent...thank you!  Works perfectly.  Since I just wanted to return the date on the “Net Installs” row I added the SELECT modifier to the metric in the IF statement:

IF('Metric'eSELECT: 'Dimension'='Dimension'."Net Installs"]>0,Month)MREMOVE LASTNONBLANK: Month]

 

Before you responded I ended up finding the article below that helped me understand better what is happening with the calculation.  I was in the middle of tinkering in the formula playground when I lucked out and saw that you provided a reply :)  Nonetheless I figured I would pass this along for anyone else that might be interested:

Get the first / last time a value appears in a metric: formula building guide

Reply


Ask your question here!

Terms & ConditionsCookie settings