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

New Contributor

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

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.

Message 1 of 6
5 REPLIES 5
Super Contributor

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

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

Message 2 of 6
Occasional Contributor

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

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

Message 3 of 6
Highlighted
Contributor

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

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.

Message 4 of 6
New Contributor

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

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!

Message 5 of 6
Community Boss

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

@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

 

Message 6 of 6