get item numbered list depending on a range of dates

RosarioCaramagno

Hi,

I need your help.

I have 3 lists: Product, rate, invoice

and two modules:

I want to put in invoices.valid rate the max number# of rate list where all following conditions are satisfied:

Rates.status = true
Invoice.Prod name = rates.prod name
Rates.start date < Invoice.Invoice date < rates.end date

Can you help me?

Thanks

Accepted answers

All comments

nathan_rudman

you need a line item "rate getter" with the invoice AND rate list and do that formula with the conditions you are mentioning:
IF all conditions THEN ITEM(rate) else BLANK
That line item needs to have a summary type "First non Blank" (you rate list must have a top level)
Then you can "extract" that rate into your valid rate line item by pointing to that rate getter line item.
Anaplan will automatically pick up the "first non blank" result of the function at the summary level of the rate list

RosarioCaramagno

Thanks, but is not clear for me (I'm a beginner in anaplan).

Could you expand the condition
"IF all conditions THEN ITEM(rate) else BLANK"
with the modules used from me?

Thanks a lot.

nathan_rudman

depends where you type it but I put the full name of modules to be safe:

IF rates.status and invoice.prod name = rate.prod name then

IF Rates.start date < Invoice.Invoice date AND Invoice.Invoice date < rates.end date THEN

ITEM(rates)

else Blank

else blank

RosarioCaramagno

Sorry but it doesn't work.
I put your formula in VALID RATE of invoices module that have format="rate list"

this is the formula
IF rates.status and

invoices.prod name = rates.prod name
then IF Rates.start date < Invoices.Invoice date AND

Invoices.Invoice date < rates.end date

THEN ITEM(rates)

ELSE Blank

ELSE blank
I receive the following error:

Have you some suggestion?

RosarioCaramagno

Sorry but I suspect that this solution doesn't work because I think that it need an aggretion formula to link two modules;
Also I need to compare the invoice date of the module INVOICE with a range of dates saved in the module of RULES.

Any other suggestions?

nathan_rudman

as I said you need to have atop level on your "rate" list.

The formula is telling you that it is typed incorrectly. please check the names of the modules and the lists. This error says the rate list isn't called "rate"

RosarioCaramagno

Thanks,
I follow your suggestion but I receive an error:

and RATE LIST have a top level:

I don't understand where is the error!

Thanks to help me.
What I wrong?

rob_marshall

@RosarioCaramagno

You can’t use item(rates list) because Rates list is not part of the Applies To.

Quick Links

Can you help?

Unanswered questions

Has anyone taken a stab at Master Anaplanner Recertification exam yet?
Just curious :)…
Solution for indent diabled for hierarchical list when the page has more than two axis
Hello fellow Anaplaners, I have a question about the UX for Anaplan for hierarchical list. If I have a list with multiple layers, say P1, P2, P3, and P4, and the workpage had 2 axis, list and line items, in its vertical axis and time in its horizontal axis (see figure below) The list is shown without indentations, and may…
Best way to Architect Financial Statements in Anaplan for Scalability
I have seen two methods used to build financial statements and I am wondering what others have experienced in scalability/maintenance between the two or if there is a better approach. Assume that we have a flat data source for our general ledger within Anaplan. Our line item we are focused on within the source data is the…