Get last occurrence value

Hello,

Is there a way I can get the last occurrence value in a numbered list?

I have multiple floor type that I need to activate the checkbox based on the latest end date. Is there a way i can create a formula here?

Thanks in advance for the help.

Regards,

Tom

Accepted answers

kavinkumar

@tompatrick.tingCheck this if it works for you.

You can make use of RANK function to achieve this. Sample below,

Hope this helps. Let me know if you have any concerns.

All comments

AlejandroGomez

hi @tompatrick.ting

1. First you need to create a list with all the unique "Floor Types" from your numbered list.

2. Then, create a module that contains the list above and a Line Item called for instance "last non blank" and Time Period formatted. The formula for this line item should be something like: source module.Last Date (time)[Lastnonblank: source module.Floor type] (just bear in mind that the Line Item "floor type" from your original module must be List formatted. The List used for formatting is the one created at step 1. Else, create a new line item in your module called "Floor Type - List", format it as list and use FINDITEM pointing to your original "Floor Type" Line item)

More instructions on Lastnonblank: https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/LASTNONBLANK.html

3. Now, in your current module, create a Line Item that retrieves the Date from the module created in step 2 with a formula similar to this: new module.Last non blank[Lookup: Floor Type] (then again remember that Floor Type must be list formatted as per explained in step 2.

4. Finally all what you need to do to "check" your boolean is to compare the date in your original Line Item "End Date (Time)" with the Time period retrieved in step 3. If Periods are equals, then true, as that would be the last occurrence.

Please, let me know if you have further questions or if you managed to get it working.

Cheers,

Alex.

www.olivehorse.com

kavinkumar

@tompatrick.tingCheck this if it works for you.

You can make use of RANK function to achieve this. Sample below,

Hope this helps. Let me know if you have any concerns.

tompatrick.ting

Thanks Kavin! It works perfectly.

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…