Hello! The SELECT modifier can be used to Offset a dimension (ex. SELECT: Month - 1), but is it possible to assign different Offset amounts based on one of a dimension of the Metric? Here is the example I am stuck at now:
I am trying to assign specific Offset amounts to a metric (0. UFR - Live) based on the Customer Payment Term dimension (ex. For any data in the Net 30 → 1 month offset, Net 60 → 2 month offset, etc)
Ideally I could assign an Offset Integer as a Property of the Customer Payment Term dimension and that would determine the Offset (ex. fSELECT: Month - ‘Customer Payment Term’.’Offset Integer’]
Is there a better and more sustainable approach rather than a massive nested IF statement? Thank you!
Page 1 / 1
Hi @connorsmith ,
This could be achieved using the Shift function with this formula:
Where Offset is a property of the ‘Customer Payment Term’ dimension:
Hope this helps,
Issam
Hi @connorsmith ,
Instead of using the SELECT modifier, you might be able to use a BY, which is a more dynamic in this case.
I've worked out an example (see screenshot) with 4 metrics. The main difference that instead of “pulling” the data with SELECT you're “pushing” the data via a mapping metric with the BY modifier.
Source: your source data (some dummy data in my case) Payment Term Offset: Just an input metric with the correct offset amounts, you could also do this in a property of your payment terms dimension if it doesn't need to be visible. I added it in the table for visibility only.
Month to push: the metric that defines the mapping from month → month. I'm using the following formula:
Month + 'Payment Term Offset'
Target: your final output with the following formula:
Source
Happy to hear your thoughts on this alternative method!
Thanks @Issam Moalla this worked well
@Stef this is also very creative. I did’t realize that the Month + 'Payment Term Offset' would work without a SELECT modifier. Are there any advantages to doing it this way rather than using the SHIFT function?
Thanks both!
@connorsmith there might be a small performance advantage (BY is usually more efficient than SELECT) but I expect it to be minimal (you could try it out though).
