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
Best Answer
-
@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.
1
Answers
-
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.
2 -
Thanks Kavin! It works perfectly.
0
