Select value from future transaction

Hi,

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!

Accepted answers

fredrickstraube

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...

Thanks!

All comments

VIGNESH.M

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.

Regards,

Vignesh M

fredrickstraube

Hi Vignesh,

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

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...

Thanks!

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.

Regards,

Vignesh M

ArunManickam

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.

Thanks

Arun

fredrickstraube

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.
Thanks!

/Fredrick

ArunManickam

Hello @fredrickstraube ,

Use this,

Transaction-Rank summary type is First Non Blank

Thanks

Arun

premkumar12

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

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…