Identifying a Line Item's Max Value

Highlighted
Contributor

Identifying a Line Item's Max Value

In the attached example, I am using a rank function in the RANK line item to identify rows with non-blank ICM_ASNMT_ID and assigning them ascending rank values. Next, I want the MAX RANK VALUE line item to search through the RANK line item and return the highest value it can find (as demonstrated). In the example, this would be 6 which I have typed into each cell. Ultimately I need a way to reference what the maximum value in RANK is in order to insert new records into this module and populate the ICM_ASNMT_ID with that number. For example, I would be able to use the fact that 6 is the highest RANK value to create a row with the ICM_ASNMT_ID of 7.

 

How can I use a formula to do this?

6 REPLIES 6
Contributor

Re: Identifying a Line Item's Max Value

To clarify, I mostly need to know how to identify the max RANK value. The creation of a new row is something I can worry about later.
Highlighted
Master Anaplanner/Community Boss

Re: Identifying a Line Item's Max Value

@RobinSilk 

Funny you should ask this. I just posted this yesterday for someone else. YOu can see it in the non-blank rank line item and the max rank line item.

Let me know if you want a more specific example. Happy to write one up.

NonBlank007.pngNonBlank008.png


Jared Dolich
Highlighted
Contributor

Re: Identifying a Line Item's Max Value

Hey Jared,

 

Thanks for your response. I feel like I am close, but potentially missing something. I've attached three screenshots of what I've built out and it mirrors what you did (with some items that were nonessential left out). Do you see what my issue is? My MAX RANK item is not successfully ignoring non-blanks, and is not showing non-maximums either. It should be showing 6 for all of them.

 

- Robin1.PNG2.PNG3.PNG

Highlighted
Master Anaplanner/Community Boss

Re: Identifying a Line Item's Max Value

@RobinSilk 

@Akhtar.shahbaz suggestion is a good one if you just want the max at the summary level.

If you want to see the max rank on all list items like I had in my grid you'll need to remove the "applies to". Just hit delete and remove the "-" that's there.

I also noticed that you're ranking all hierarchy levels. If your intention is to rank the lowest level then you'll probably need to use the group by variable in the RANK function (4th variable). If you need help, just say the word.

Between @Akhtar.shahbaz and I we can help you.


Jared Dolich
Highlighted
Contributor

Re: Identifying a Line Item's Max Value

Hey Jared,

 

My list currently does not have a top-level item, which I think is where this might be going wrong. I will add one and see if that works.

Highlighted
Super Contributor

Re: Identifying a Line Item's Max Value

Hi @RobinSilk 

 

I have identified the max value of rank by three method, which one is best in your case you can use that.

Screenshot_125.png

 

 

Screenshot_126.png

 

Make sure about the summary

Screenshot_127.png

 

Hope this helps!

 

Thanks

Akhtar