Formula to number a line item to create an order 1,2,3 etc.

Dahya
Dahya
in Modeling

I am creating a module that will be a template to import into our ERP. One of the line items is called 'Journalnum' which essentially needs to start at 1 and every line thereafter will be 2, 3 4, etc. E.g. in excel cell A1 is journal number 1 and cell A2 will be a formula to equal A1+1. There are 1000+ lines in the template.

Tagged:

Answers

  • LipChean_Soh
    LipChean_Soh

    Hi Dahya,

     

    I'm afraid you'll have to be more specific about what you're trying to achieve, and what difficulties you're facing before we can help you.

     

    Thanks,

    LipChean

  • DmitrySorokin
    DmitrySorokin

    Hi Dahya, 

     

    I am not sure if there is a way to create list members based on formula. In Anaplan you can create list members by importing data or mannually populating. 

     

    You could create the logic you described in Excel - populate list with member 1 to 1000. And then import or copy paste them to Anaplan. 

     

     

    Dmitry

  • hendersonmj
    hendersonmj

    I agree with previous response that we really need more information from you.  I've concocted my own example situation to illustrate one approach:  

     

    Suppose we want CSV exported output to look like this

    RowNumber,Product,Customer,Store,Jan,Feb,Mar,  ... 

    "1","Hats","Allen, J.","Store 001",5,3,2, ...

    "2", "Gloves","Baker, B.","Store 001",18,12,6, ...

    "3", "Coats","Carter, R.","Store 002",11,5,7, ... 

    etc...

     

    1) Use pivot / filter / show / hide in order to display your module in the above arrangement, but without the RowNumber dimension. Save this View as <Module name> - Staging View"

     

    2) Create a numbered list named "RowNumber".
     - Add properties (format: list) named for each dimension (Product, Customer, Store) in the final output. 
     - Add a property (format: Number) named  "List_Rank_Num" and give it a RANK formula. 
     - Add another property (format: Text) named "RowNum" and with a formula

       TEXT(List_Rank_Num) 
     - Set the Display Name property of the RowNumber list to "RowNum".  

     

    4) Import from your saved "... Staging View" from step 1 into the "RowNumber" list with each item identified as unique by dimensiona Product, Customer, and Store.  Each item (corresponding to a row of the desired output) will get a serial number assigned by the system as its Name.   (Be sure to clear the list before each time you repeat this process.) 

     

    5) Create a new module name "CSV Output" with one List dimension: RowNumber

    Add the following Line Items:

    Product  (format: List, Product) formula = RowNumber.Product

    Customer  (format: List, Customer)= RowNumber.Customer

    Store  (format: List, Store) formula = RowNumber.Store

    Jan  (format: Number)

    Feb  (format: Niumber)

    Mar  (format: Number) 

    ...  and so on for the other months. 

     

    6) Import data from your view (map period columns to months) into CSV Output module, or use formulas in Jan, Feb, Mar that handle the mapping.

     

    7) Have a beer.

  • Dahya
    Dahya

    Thank you both for the above, I think that's you've explained it very well. I will give it a go and let you know if this answers my question. Thank you!

  • DavidSmith
    DavidSmith

    @Dahya

    What is the dimensionality that determines the journal order?

    You can use the cumulate function to cumulate over a list

    CUMULATE(1, FALSE, list name)

    It does only work in the original order the list items were created, but it might be an alternative

    David

     

Categories