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
Answers
-
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
0 -
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
0 -
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...0 -
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
0
