Design a line item with a sequential numbers in it
Hello everyone. I hope you are doing well. I was wondering if someone could help me to design a line item in a module dimensioned by time which generates these numbers from the first column to the end column. The order of numbers and the numbers themselves are as below :
1,2,2,3,3,3,4,4,4,4, 5,5,5,5,5 and so one ( it should continue till the end column )
Thank you everyone
Best Answer
-
Great, so we can use the next function to refer to the same line item. Usually it gives circular references, but it's good to know that's not the case with the next function.
1
Answers
-
Hey, Interesting problem statement, and I got a solution. So, this is an example of arithmetic progression and there is a formula to calculate the the end term of a number if you see the pattern closely. So let's say
1 will end on 1st term
2 will end on 3rd term
3 will end on 6th term
4 will end on 10th term
Formula to calculate the end term of a number (n) is n(n+1) /2
We can create a list called Sequence and add a few items in it and we can calculate it's corresponding end term in the system module.
Sequence List
SYS Sequence Details
This must be your main module with time dimension.
We have sequence line item which will give you sequence of 1,2,3 across months. Using find item we convert the sequence to Sequence list format. And then lookup the End Term of each sequence.
Using post function, I pushed the values to the end term.
Now a final transformation to fill in the next available values to the months which is showing zero in Post Line item. Here is the formula:
I was not able to come up with the best way of writing this in such short time.. But in order to get to a solution, just nested it in a same formula. So I keep it up to you/ community members to find alternatives to get this final calculation in better ways.
Hope this helps!
1
