Creating a running sequence of numbers in Anaplan
Hi,
Am wondering if there's a way to create a running sequence of numbers in Anaplan.
Am trying to create a sequence of numbers that run from 1 to N in columns such that 1 is in col 1, 2 in col 2 ... N in col N.
Reason am doing this is to see if there is a way to copy a certain cell value (say a number) from the left most column (col 1) to the right most column (col N). This can be done with say OFFSET or LAG but that will need to reference an offset value hence the running sequence.
This would be easy in Excel and am wondering how we do this in Anaplan. I have checked the formulas but do not see a formula that does this.
Any guidance would be most appreciated.
Kind regards,
Clarence
Best Answers
-
The count would start from the first period of the time frame whether its months or weeks.
2 -
To specify an end and start then you need to create a separate module where you state the start time period and the number of columns to be numbered.
Therefore, create a new module containing no dimensions and add two line items; Start period (Time period format ) and Number ( Number format )
Update the Count Reference to include a nested IF THEN ELSE.
=IF ITEM(Time) = NewModule.Start Period AND PREVIOUS(Previous Running Count) + 1<=NewModule.Number THEN PREVIOUS(Previous Running Count) + 1 ELSE 0
This should ensure that the running count only starts where the user specifies and only continues to the specified number of time periods into the future.
1
Answers
-
Hi @ClarenceAndre ,
Are the columns you are referring to a list? If so, try creating a line item with that list in the Applies To, format it to number and use this formula: RANKCUMULATE(1, 1, ASCENDING).
It should count from 1 to N for each member of your list.
Hope this helps,
Bruno
1 -
If you are looking to create a list you could accomplish this by creating a time range that is large enough to capture the maximum number of possible columns.
Then create a module only dimensioned by this time range.
Create a line item called running count and use the following formula =PREVIOUS(Running Count) + 1
This will run from the first time period to the last adding 1 to the running total.
Create a saved view and use this to create your list.
Create a property for you list to hold the number value and map the running count line item to the list name and property.
Regarding what you would like to use this list for please can you add more detail.
Are you looking to include time in the process? OFFSET and LAG require a time dimension.
If so, then you are moving data between dates rather than a running sequence of columns. You will not need to create a separate list.
If not, then you will need to use LOOKUP to map the data from one column to another with the criteria for the LOOKUP setup as a drop down list formatted line item from which the user can define the target.
0 -
Dear @ChrisAHeathcote
See attached screenshots.
This is what I had in mind and how I solved it.
May not be the most elegant way - happy for any improvement suggestions.
I wanted to copy a specified number across columns hence the question about a running sequence.
Eventually I settled on manually keying into the line item a running sequence of numbers e.g. 1 to 5 of the columns I wanted the number copied into via a simple lag formula.
I had seen the PREVIOUS formula you mentioned but could not quite figure out how to use and after seeing your explanation am still not clear. From the Anaplan documentation, I thought the way it works is to take the difference between adjacent time periods e.g. Profit Change = Net Profit - PREVIOUS(Net Profit).
Basically if I wanted to create a sequence of numbers from say 1 to 20 short of doing it manually like mentioned above, is there a formula approach for doing so?
You had mentioned in your proposed solution creating a line item called Running Count but what would you have in Running Count? If you had all zeroes, PREVIOUS(Running Count) + 1 would be just 1? And what happens in the first column in a time range when there is no previous column? Does the formula then break down?
Kind regards,
Clarence
0 -
Dear @bletarte
That looks like a sensible solution - let me explore.
See attached screenshots on what I am trying to accomplish.
No the columns are not in a list. They would be a time range and the number of columns would be user specified. In the example provided the user is copying "3" across five columns from cols 1 to 5. I solved via a simple lag formula.
If I used RANKCUMULATE is there a way of specifying a limit e.g. 5 columns such that the number I specified would be copied across just 5 columns or any other number I specified?
I have a feeling your formula is headed in the right direction and have briefly seen it in the Anaplan technical doc and will need to study it now to see if it meets my requirements.
Again thank you for taking the time to respond.
Kind regards,
Clarence
0 -
Perfect! Thanks @ChrisAHeathcote
0 -
Thanks so much Chris!0
-
It is super easy.. Thanks!
@ClarenceAndre u can also tryout in your module and yes just RANKCUMULATE gives u expected result..
0 -
Many thanks @bletarte
I can see what you're doing.
With RANKCUMULATE can one set a limit as the solution seems to run the entire time range?
@ChrisAHeathcote provided a solution although it's a bit complicated while I've done by in a different way just by entering numbers into columns sequentially e.g. 1,2,3,4,5 with a LAG formula so the desired value gets copied across the specified number of columns (5 in this example).
The idea is to mirror what Excel is doing when one can just drag (or cntrl+shift+right) to wherever the want the value to end.
A fixed pattern following say a defined time range is what I'm trying to avoid as flexibility is required in specifying where the copied value ends across the columns.
Kind regards,
Clarence
0 -
The Rankcumulate formula goes as follows:
RANKCUMULATE(Cumulation values, Ranking values [, Direction] [, Include value] [, Ranking groups])
https://help.anaplan.com/en/4727e9c5-4687-45ec-a4a0-420d30476d42-RANKCUMULATE
You have the ability to include/exclude cells using a Boolean-formatted line item on this section of the formula: [, Include value]. The formula could look like this: RANKCUMULATE(1, 1, ASCENDING, Include?).
Are you trying to create multiple sequences on that single line item? If so, I would probably use the Previous formula as it gives you more flexibility.
0 -
Many thanks @bletarte
Yes there could be multiple sequence on the same row because the planner could be entering different values along the time horizon and have that copied for however many weeks that's required.
Let me try RANKCUMULATE(1, 1, ASCENDING, Include?)
If I understand correctly that would be referring a boolean line item Include? that defines the termination point? For example if I wanted to copy a value for 5 columns I would check the the column that is 5 weeks after the current?
Kind regards,
Clarence
0 -
Hi @bletarte
My RANKCUMULATE is just adding 1s instead or stepping them up by 1 in each column although the Include? is a nice feature as I can now specify the termination point of the series. What am I doing wrong?
Kind regards,
Clarence
0
