Is there any way or structure for me to access the value of a dimension from a Metric?
Ex. I have a Number metric that has ARR dimensioned by Customer ID and Month. I would like to find the first month a customer had an ARR value. How would I be able to access the value of the Dimension. When using firstnonblank I only am able to get the value of the Metric and cannot access any of the dimension attributes.
Answer
Accessing Dimension Value From a Metric
+9- Master Author
Best answer by Elliot
Hi
Good question - the easiest way to return an item of a dimension from a metric (instead of the value) is to use an IF Statement. Since you’re only interested in the month value of the first month with values we can use the formula below:IF ( Isdefined( ARR Metric), Month)[remove firstnonblank: Month]
Your Target metric should have only the Customer dimension.
In a general where you want to return a dimension’s item and keep all dimensions within the block you would do:
IF ( [Insert desired logic], Dimension)
The formula above says “IF there logic statement is true, then return the Dimension’s item, otherwise blank.
I hope the above helps, let me know if you have further questions
+9- Employee
- Answer
Hi
Good question - the easiest way to return an item of a dimension from a metric (instead of the value) is to use an IF Statement. Since you’re only interested in the month value of the first month with values we can use the formula below:IF ( Isdefined( ARR Metric), Month)[remove firstnonblank: Month]
Your Target metric should have only the Customer dimension.
In a general where you want to return a dimension’s item and keep all dimensions within the block you would do:
IF ( [Insert desired logic], Dimension)
The formula above says “IF there logic statement is true, then return the Dimension’s item, otherwise blank.
I hope the above helps, let me know if you have further questions
+13- Employee
When I do this I tend to use something like Month[ADD: Customer][FILTER: ISDEFINED(ARR)][REMOVE FIRSTNONBLANK: Month]
You can try each steps in the formula playground, but basically with Month[ADD: Customer], you’re creating a matrix of Month x Customer where every cell has the Month as the value, then filter to keep only the values where there’s an ARR. I’ve used ISDEFINED because it’s most efficient but you could use any other condition e.g. ARR > 0 or ARR >= ThresholdMetric. You then remove the Month dimension by taking the first non blank value.
It’s probably a bit more abstract than Elliot’s method though.
Tell me if it’s working for you!
François (Pigment)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.