Highlighted
Contributor

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.

 Capture.PNG

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!

6 REPLIES 6
Highlighted
Master Anaplanner/Community Boss

Re: Select value from future transaction

Hi Fredrick,

 

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

 

Step 1: Create a Rank List as shown below

001.png

 

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

1.png2.png

 

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

3.png

 

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

4.png

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

1.png

 

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

 

Regards,

Vignesh M

Contributor

Re: Select value from future transaction

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:

Capture.PNG

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

Thanks!

Highlighted
Master Anaplanner/Community Boss

Re: Select value from future transaction

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.

001.png

 

Correct me if I understood differently the requirement.

 

Regards,

Vignesh M

Highlighted
Community Boss

Re: Select value from future transaction

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

Highlighted
Contributor

Re: Select value from future transaction

 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 

Highlighted
Community Boss

Re: Select value from future transaction

Hello @fredrickstraube ,

 

Use this, 

clipboard_image_0.png

Transaction-Rank summary type is First Non Blank

 

clipboard_image_1.png

 

Thanks

Arun