get item numbered list depending on a range of dates

Hi,

I need your help.

I have 3 lists: Product, rate, invoice

 

lists.JPG

and two modules:

mods.JPG

rates.JPG

invoices.JPG

 

 

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

 

Answers

  • 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



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

  • 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

  • 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:

    ERROR.JPG

     Have you some suggestion?



  • 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?

  • 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"

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

    RosarioCaramagno_0-1608329796370.png

    and RATE LIST have a top level:

    RosarioCaramagno_1-1608329944751.png

     

    I don't understand where is the error!

    Thanks to help me.
    What I wrong?

     

  • @RosarioCaramagno 

     

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