Expand my Community achievements bar.

Join us for the next Community Q&A Coffee Break on Tuesday April 23, 2024 with Eric Matisoff, Principal Evangelist, Analytics & Data Science, who will join us to discuss all the big news and announcements from Summit 2024!
SOLVED

## Calculated Metrics - BETWEEN function or Combining two Great Than or Equal to logic pieces of logic

Level 2

I'm trying to sift through some noise in my freeform panel. There's a metric I'm using and the data is fairly volatile. I'm trying to use a calculated metric to filter/sort my data for me. Is it possible for me to build a calculated metric that allows me to have "BETWEEN" logic in it?

It would be great if I use Greater Than or Equal To X then join it with an AND Less Than or Equal To Y. But I don't see a straightforward way of doing that in the builder (please correct me if I am mistaken). In lieu of that, is there a BETWEEN like function or approach I can take in the builder? For sake of example let's say I wanted the freeform panel to show me pages that have more than 100 entries and exits lower than 50, how can I achieve this metric? Or if you think there's a better approach than going the calculated metric route please let me know.

1 Accepted Solution

While not the easiest to accomplish... you can make a custom metric looking for these scenarios using nested IF statements:

IF Entries >= X Then

IF Exits <= Y Then

Static Value = 1

Else

Static Value = 0

Else

Static Value = 0

In this case, the first IF checks for Entries... if it matches it goes on do another IF Statement (if not it returns a boolean false or 0 value); then in the second IF you check the Exits... if it matches then set a boolean true or 1 value, and if not, once again set to 0

Here is a (small - sorry) screen shot of a similar calculated metric:

and the summary:

Now, what will happen if you use this metric with your Pages as the breakdown is the following:

"1" will appear next the pages that match your criteria, and "0" will be on the ones that don't... this doesn't actually filter the list of pages, but you can sort, or you might be able to create a segment looking for your custom metric to be 1? I didn't take my sample that far, as I didn't save this custom metric as a permanent, reusable metric that I can pull into a segment....

You are right though, it's not exactly straight forward... but hopefully this can get you started.

4 Replies

While not the easiest to accomplish... you can make a custom metric looking for these scenarios using nested IF statements:

IF Entries >= X Then

IF Exits <= Y Then

Static Value = 1

Else

Static Value = 0

Else

Static Value = 0

In this case, the first IF checks for Entries... if it matches it goes on do another IF Statement (if not it returns a boolean false or 0 value); then in the second IF you check the Exits... if it matches then set a boolean true or 1 value, and if not, once again set to 0

Here is a (small - sorry) screen shot of a similar calculated metric:

and the summary:

Now, what will happen if you use this metric with your Pages as the breakdown is the following:

"1" will appear next the pages that match your criteria, and "0" will be on the ones that don't... this doesn't actually filter the list of pages, but you can sort, or you might be able to create a segment looking for your custom metric to be 1? I didn't take my sample that far, as I didn't save this custom metric as a permanent, reusable metric that I can pull into a segment....

You are right though, it's not exactly straight forward... but hopefully this can get you started.

Level 2

Thank you Jennifer! I will try this approach, I believe it will work. I will reply back once I've given it a go.

Level 2

@Jennifer_Dungan worked like a charm! I'll use this for future inspiration as well.

Thank you for the help!

Yay! Glad it helped you out. The nested IFs can be a little odd, but it does work...

OH I just realized... there is a AND available inside the logical test... now why couldn't I find that when I posted before.....

You can give that a try too... it might save on nested IFs