Find the minimum 'Month' across multiple Line Items within the same module

Hi,

Apologies if this doesn't make much sense but I am fairly new to Anaplan.

I have a module with multiple line items, each retrieving a month from other modules (Formatted to Time Period: Month). I would now like another Line Item (within the same module) to calculate the 'MIN' of those line items. Is this possible?

I have tried MIN('Line Item 1', 'Line Item 2', 'Line Item 3') but this comes up with a formatting error (even though it is formatted as Time Period: Month)

Any help would be greatly received.

Regards

Carly

Accepted answers

anikdas

Hi @CarlyBond

Was trying to think of an elegant solution. This actually is a kind of problem where "we need to find the minimum excluding certain values from calculation".

Below is what i could come up with:

Continuing with my previous solution, Create just one more line item as date formatted. I called it "Blank Day"
You can populate this line item with any date greater than the end date of your model timescale

Then use the following formula:

PERIOD(MIN(IF ISBLANK('Line Item 1') THEN 'Blank Day' ELSE START('Line Item 1'), IF ISBLANK('Line Item 2') THEN 'Blank Day' ELSE START('Line Item 2'), IF ISBLANK('Line Item 3') THEN 'Blank Day' ELSE START('Line Item 3')))

This works for the situation when multiple of the line items are blank. If all are blank, this returns blank.

Screenshot 2020-06-19 at 10.39.54 AM.png

Hope this helps!

Thanks

Anik

All comments

anirudh

Hi Carly,

Create an equivalent number of number formatted line items as the number of month formatted line items
Use the formula MONTH(Month1LineItem) for the first line item
MONTH(Month2LineItem) for the second line item and so on

Finally, create a min line item and point it to the newly created number formatted line items.

MIN(MonthNumberLineItem1, MonthNumberLineItem2......)

Additionally, if you need it, create another line item to format the minimum month number back to month

Let me know if you need more help

Regards,
Anirudh

anikdas

Hi @CarlyBond

Here is a solution to this. Write the below formula -

PERIOD(MIN(START('Line Item 1'), START('Line Item 2'), START('Line Item 3')))

Here line item 1, line item 2 and line item 3 - all month formatted along with the result line item.

Let me know if this works.

Thanks

Anik

Kanishq17

@CarlyBond

You need to convert month into dates using start() , then get the minumum date in another line item, then get month back using period()

here l1,l2,l3 are your month time formatted line items, get minimum date in min date which is date formatted, then convert date to month using period function

attaching blueprint

CarlyBond

Thank you for coming back to me so quickly 🙂

This formula works, however, it includes the blank values. Is there any way in which I can ignore them?

Apologies, I should have advised there were blank values in the first place.

anirudh

Hi Carly,

Try this:

MAX(BLANK, PERIOD(MIN(START('Line Item 1'), START('Line Item 2'), START('Line Item 3'))))

That is, just do a MAX to the existing MIN line item

That should work

Regards,

Anirudh

CarlyBond

I don't think I have explained myself very well.

Within Line Item 1, 2, 3, 4, etc there will always be a BLANK cell (i.e. There will only be one or two of the line items with something in them) so the MIN function is always returning a BLANK.

Therefore the new MAX function is always returning BLANK too.

anirudh

Think I got you now!

Can you try this:

1. Follow my approach and have 4 number formatted line items. Formula:

IF ISBLANK(MonthLineItem) THEN 100 ELSE MONTH(MonthLineItem)

2. Write your minimum formula referring the number formatted line items. The BLANK months will never show up as their value is 100

3. Convert MIN line item back into Time

Regards,

Anirudh

anikdas

Hi @CarlyBond

Was trying to think of an elegant solution. This actually is a kind of problem where "we need to find the minimum excluding certain values from calculation".

Below is what i could come up with:

Continuing with my previous solution, Create just one more line item as date formatted. I called it "Blank Day"
You can populate this line item with any date greater than the end date of your model timescale

Then use the following formula:

PERIOD(MIN(IF ISBLANK('Line Item 1') THEN 'Blank Day' ELSE START('Line Item 1'), IF ISBLANK('Line Item 2') THEN 'Blank Day' ELSE START('Line Item 2'), IF ISBLANK('Line Item 3') THEN 'Blank Day' ELSE START('Line Item 3')))

This works for the situation when multiple of the line items are blank. If all are blank, this returns blank.

Screenshot 2020-06-19 at 10.39.54 AM.png

Hope this helps!

Thanks

Anik

CarlyBond

Thank you so much, this does work.

anikdas

Glad it works, can you mark my response as a solution for future reference?

Quick Links

Can you help?

Unanswered questions

Where can I find export file after running export process using machine task in a workflow?
I am testing export actions using workflow but its not generating any file when I am running the workflow, but the same process is generating export file when ran from the Actions→Process section. Is their any issue with the workflow functionality? Also, If I want to generate a file after completion of workflow and pull…
Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…