Identifying the max value for a specific field

DeveloperCYT

Hi, i do like a dilemma it seems...

I have a list with 2 fields (well lots but 2 that matter) Bvfsku and Vintage

I want to add a field that indicates the one that has the latest vintage (2018 in this example)

So a boolean or a number field that is set to TRUE or 1 if the Vintage is the latest year....

How can i do that please?

Answers not on postcard please, thanks in advance

Kev

Accepted answers

VIGNESH.M

Hi,

Your Bvfskus column should be list formated to get this. If your column is text format, create another line item and convert into List format using FINDITEM.

Try this.

Regards,

Vignesh M

All comments

VIGNESH.M

Hi,

Use Max as summary of the vintage line item and use the below logic.

Regards,

Vignesh M

DavidSmith

@DeveloperCYT

1. Set up a Lookup module (no dimensions) in which you set the vintage year. You can calculate this from the Current period (if that is what you want)

In the above, Target year is formatted as Year and Year is formatted as a number

I hope that helps

David

DeveloperCYT

but i have 2000 Bvfskus, so i need to find the maximum year for each Bvfsku, not all bvfsku's

DeveloperCYT

Sorry David, i don't need to find the current year, i need to find the last vintage for each bvfsku (We have 2000+) so if the last was 2016 then i can identify it as such.

DavidSmith

@DeveloperCYT

Have a look at this

https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/xMAX_y.html

I think this is what you need - You might need another line item to hold the name/code of the list item and then bring that through. You could then use that to match

You might also need the FIRSTNONBLANK aggregation to move the formatted list items between modules

Have a look here too

https://help.anaplan.com/anapedia/Content/Calculation_Functions/CF_Aggregation_Functions.html

Hope this helps

David

VIGNESH.M

Hi,

Your Bvfskus column should be list formated to get this. If your column is text format, create another line item and convert into List format using FINDITEM.

Try this.

Regards,

Vignesh M

kavinkumar

HI @DeveloperCYT,

Try my below approach!!

Step 1: Create below line items in your module and apply the formula.

Identifying the max value for a specific field.PNG

Step 2: Create a module with 'Bvfsku' list and create one line item.

Identifying the max value for a specific field 1.PNG

End Result:

Identifying the max value for a specific field 2.PNG

Note: 1. You should have 'Bvfsku' list if not please create. 2. If you have 'Year' as a number formatted then you can directly refer else convert by the above approach.

I hope this helps!

Regards,
Kavin.

DavidSmith

@DeveloperCYT

2 simple solutions depending on your list structure

1. Assuming you have a composite list

You can use the MAX summary method and a LOOKUP on the parent of the list

2. Assuming it is not a composite list, but you still have the attibributes

Create a module by the attribute using the MAX aggregate parameter and then use a LOOKUP similar to 1.

Hope that helps.

David

Quick Links

Can you help?

Unanswered questions

AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…
Using time for something not time related - special characters :)$
We had a recent challenge with the need to "clean" invoice data descriptions from special characters. This was done previously in SQL with the a long convoluted formula "REPLACE(REPLACE(REPLACE('Ana&plan', '&', '') that would have to edited by a developer in case any new special characters came through. Using the replace…