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.

Accepted answers

All comments

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

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

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

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

@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

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…