Register

Formula using Min Function

neg177
Regular Contributor

Formula using Min Function

Hi,

 

I'm trying to replicate this Excel Function in Anaplan. I want to make sure my Anaplan Function is picking up all the values for my Line Item from the begging through the end of the month, and not just the beginning and end month numbers. I have the Formulas below. Please advise. 

 

Excel Function

=IF(MIN(Securitization!$RW$33:$SP$33)<0,1,0)

 

Analpan Function 

IF MIN('Y03 Securitization'.'Securitization Master Acct (x5000 Closing)'[LOOKUP: 'S01 Time Settings'.Start of the Month], 'Y03 Securitization'.'Securitization Master Acct (x5000 Closing)'[LOOKUP: 'S01 Time Settings'.EOM Date]) < 0 THEN 1 ELSE 0

 

Thank you 

6 REPLIES 6
Misbah
Moderator

Re: Formula using Min Function

@neg177 

 

Use MIN as an aggregation function. Try this

 

IF 'Y03 Securitization'.'Securitization Master Acct (x5000 Closing)'[MIN: Actual Date] < 0 THEN This ELSE that

 

Hope this helps

Misbah

 

 

neg177
Regular Contributor

Re: Formula using Min Function

This function is not working, also I'm not sure how the logic for picking up the min value with the start and end of a month. 

Still confused on how this function will pick up the min value within a certain month for my Line Item. 

 

IF 'Y03 Securitization'.'Securitization Master Acct (x5000 Closing)'[MIN: Actual Date] < 0 THEN This ELSE that

Misbah
Moderator

Re: Formula using Min Function

@neg177 

 

Care to attach screenshots. That would really help to expedite

ChrisHeathcote
Community Boss

Re: Formula using Min Function

@neg177 

Again use minimum time summary option to pull out the minimum value for a time period in which the source data sums into.

 

x[MIN:y] requires that the target module contains the dimension y which is a list formatted line item in the source. It is y that is used to map into the dimension present in the target. 

Therefore, the minimum value of x is returned relative to the mapping y where y is a dimension in the target. 

 

I dont believe this fits your need as you are looking to calculate the minimum value for a time period which is the parent of the time period used in source; days/weeks into months.

 

Good luck,

 

Chris 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
MarkWarren
Expert

Re: Formula using Min Function

You may need to add a List that represents the Timescale you're aggregating over, with a mapping to the real Timescale in a module.
Use MIN against this "fake time" list for values in the mapping module.
Will then need to do a mapping lookup to get those values into your target...

ChrisHeathcote
Community Boss

Re: Formula using Min Function

Another option here is to use MOVINGSUM and the aggregation method MIN.

 

Start and end period is relative the time period so you will need to calculate these as inputs if you want each period to aggregate across the full year.

 

Example, Jan would start at 0 and end at 12 while Mar would start at -2 and end at 10 ( 2 periods back (Jan) and 10 periods forward (Dec))

 

Using a time system module set up for period create a line item set with a formula of 1.

Create a second line item where you take the current period value from the first line item and add the previous value. Use an IF..THEN..ELSE function to reset the count when it equals 12.

Create a third and fourth line item to calculate the start and end position to use in your MOVINGSUM.

start = 12 - (12 - period number + 1 ) Jan = 12 - ( 12 - 1 + 1 ) = 0, Mar = 12 - ( 12 - 3 + 1 ) = -2 

end = (12 - period number + 1)  Jan = ( 12 - 1 + 1 )  = 12, Mar = ( 12 - 3 + 1 ) = 10  

 

Good luck,

 

Chris 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA