Identifying a Line Item's Max Value

RobinSilk
RobinSilk
in Modeling

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?

Tagged:

Answers

  • RobinSilk
    RobinSilk
    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.
  • JaredDolich
    JaredDolich

    @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

  • Akhtar.shahbaz
    Akhtar.shahbaz

    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

     

  • RobinSilk
    RobinSilk

    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

  • JaredDolich
    JaredDolich

    @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.

  • RobinSilk
    RobinSilk

    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.

Categories