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

Accepted answers

All comments

Misbah

@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

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

@neg177

Care to attach screenshots. That would really help to expedite

ChrisAHeathcote

@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

MarkWarren

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

ChrisAHeathcote

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

Quick Links

Can you help?

Unanswered questions

L2 Import DEM01
Hi all, i'm trying to import the file DEM01 csv but i don't know how to do it How do i select th evalue of DEM01 if the time value is on column? thanks a lot Andrea
ADO File Upload - Semicolon Delimiter
Hi, Based on the regional settings, my default delimiter/separator for CSV files is a Semicolon. In the classic way of uploading a csv file, I was able to select the delimiter. When using ADO, I don't have the option to select a delimiter. Uploading a Semicolon delimited file returns an error, or an upload with only one…
Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…