The idea of using a calculated field with IF and ELSEIF statements isn't new, but I wanted to make it easier for people to do, without having to write the code from scratch each time. So I've made a template (here) that uses Google Sheets.
All you need to do is:
- Make a copy of the sheet
- Add in the rules for your IF and ELSE statements in columns A and B
- Change the dimension name in cell F5
- Copy the equation in cells F7:F37 and paste into your calculated field
In case you aren't familiar with this technique, it will allow you to dynamically group up names in a Dimension based on whether they contain certain string or numbers. So if you have an IF statement with the following code, you will group up every row that contains 'old' in the Product dimension.
IF CONTAINS([Product], "Old") Then "Old Products"
This can be very helpful as it means you don't have to manually keep on top of grouping up Dimension names, which is time consuming, can lead to human error and makes it hard to handover.
I've also included an "other' label, which lets you easily see when certain names fall outside of your logic - allowing you to easily update it to capture the new parameters.
Last thing, the equation logic works its way down from the top, so if a row falls into two labels, the first one will be applied.
Hope that helps and let me know if you have any questions.