SELECT Modifier with Dynamic Offset

  • 10 June 2024
  • 4 replies
  • 137 views
C
Rank
Userlevel 1
Badge +3

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. [SELECT: Month - ‘Customer Payment Term’.’Offset Integer’]

Is there a better and more sustainable approach rather than a massive nested IF statement? Thank you!

 

4 replies

Issam Moalla
Rank
Userlevel 5
Badge +9

Hi @connorsmith ,

This could be achieved using the Shift function with this formula:

'UFR - Live'[BY CONSTANT: SHIFT(Month, - 'Customer Payment Term'.Offset)]

Where Offset is a property of the ‘Customer Payment Term’ dimension:
 

Hope this helps,

Issam

Stef
Rank
Userlevel 4
Badge +6

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 [BY SUM: -> 'Month to push']

 



Happy to hear your thoughts on this alternative method! 

Kind Regards, Stef
C
Rank
Userlevel 1
Badge +3

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!

Stef
Rank
Userlevel 4
Badge +6

@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). 

Kind Regards, Stef

Reply


Ask your question here!

Terms & ConditionsCookie settings