Get last occurrence value

Highlighted
Occasional Contributor

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?

tompatrickting_0-1594818369775.png

 

Thanks in advance for the help.

 

Regards,

Tom

3 REPLIES 3
Highlighted
Certified Master Anaplanner

Re: Get last occurrence value

hi @tompatrickting 

 

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

Highlighted
Master Anaplanner/Community Boss

Re: Get last occurrence value

@tompatricktingCheck this if it works for you. 

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

image.png

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

 

Regards,
Kavin.
Highlighted
Occasional Contributor

Re: Get last occurrence value

Thanks Kavin! It works perfectly.