Select value from future transaction


I have a module with a number of transactions for different entities. For all of these entities, I have a Unique ID (UID) and a valid from date. I also have a line item that uses the RANK function to rank the transactions. See picture below.


Now to the issue; I need a value in the Valid To line item. This value should be the same as the Valid From for the next transaction. For example, The Valid To for the row #5721 should be 3/27/2019. 

My initial idea was to do something like this: Valid To = Valid From [LOOKUP: UID, LOOKUP: Rank order+1] but I didn't manage to get that to work.

Does anyone know how to tackle this issue? Any help is appreciated!

Thanks a lot!

Best Answer

  • Hi Vignesh,

    Thanks a lot for your answer! It is really close to what I need, there is just one thing missing:

    In my original module, I have several different object, hence why I have a line item called UID. In the print screen I added, I had a filter to show just one UID. Somehow, I need to incorporate that into your solution. How would you do that?

    See my results in the image below:


    As you can see, I have a few different UID's but they all get the same result...



  • Hi Fredrick,


    Try this solution, but you need to create 1 list and 2 modules.


    Step 1: Create a Rank List as shown below



    Step 2:  Create 3 New line item(Next Item, Rank List & Next Rank List) and format the line item as shown below



    Step 3: Create a module as Rank Mapping 1 and Add line item as Rank

                Applies To: #Transaction List and Rank List

                Format: #Transaction List : Summary: FIRST NON BLANK

                Use the below formula



    Step 4: Create a module as Rank Mapping 2 and Add line item as Rank

                Applies To: Rank List

                Format: #Transaction List : Summary: NONE

                Use the below formula


    Step 5: Write the formulas now and check the result.



    If you find any other alternate solution please pass it here.



    Vignesh M

  • Hi Fredrick,


    If you want to apply this rule only for the set of data, then apply the rank only to the transaction which meets the criteria.


    In my below example, I want to only apply the rule to UID- #5 data sets, so created flag line item and right a logic to flag only the records which you want and include that flag condition with Rank.



    Correct me if I understood differently the requirement.



    Vignesh M

  • Hello @fredrickstraube 

    Since your rank is by UID. On top of @VIGNESH.M  solution, in his Rank Mapping 1 & 2 modules, you need to add UID as a dimension. Then you need to lookup for Next rank and lookup for the same UID.




  •  Hi Arun,

    Thanks a lot for you answer. How would I get the mapping modules to take the UID into account after adding the additional dimension? In my original model, the UID is a list formatted line item.


  • Hello @fredrickstraube ,


    Use this, 


    Transaction-Rank summary type is First Non Blank






  • If the Transactions list having parent hierarchy the line item "Transcation Rank" it's asking for top level after giving the top level to that list, after that also its showing "Built in top level not present" like this error i got..



    Pls give any suggestions