nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Code with the largest total value for Invoice Num, Supplier

Teresa Modesta

I am on a site where we have no access to the rpd and are not aloud to use the evaluate function, so I am hoping this is possible with just answers standard OBIEE functionality.

my data is like this:

code amount Invoice Num Line Num Supplier

122 200 12345 1 John Dee

133 10 12345 2 John Dee

133 1 12346 1 Jane Doe

134 1 12346 2 Jane Doe

The output I want is

code amount Invoice Num Supplier

122 210 12345 John Dee

133 2 12346 Jane Doe

The logic for the result is 'give me the code by Invoice Num and Supplier that has the largest total value, in the case of ties give me an arbitrary result like the lowest line num for the greatest joint amount - or just a random line of the tied greatest, I am easy.

Many thanks guys and girls in thanks for your help.

Teresa.

Find more posts tagged with

Accepted answers

All comments

Joel Acha

Create a column formula using RANK on Amount BY Supplier & Line Number, that should handle all of your scenarios and also a column to return the code using similar logic

Teresa Modesta

Hello Joel,

I am looking at rank syntax and just not understanding how it helps me get the CODE for an Invoice Num / Supplier that has the Max(amount) by Invoice Num / Supplier

To be clear I want the TOTAL (Amount) but I only want the CODE for the line where Max(Amount by Invoice_Num, Supplier) = Amount

Is there a way?

If so could you spell out the syntax you had in mind using RANK?

Joel Acha

RANK code BY invoice num, supplier name and you can have a filter on your analysis where this column = 1

You may need to change your analysis to a union to do the 2 calculations in separate steps because of the differences in filters.

Teresa Modesta

RANK code by invoice_num, supplier name will get me the largest code, I want the line where the code has the largest total value in the Amount Column, and I want to see the total amount - unfiltered.

Does that make sense?

Joel Acha

Didn't fully understand your requirement - should have read it properly! Amount & Code logic:

SUM(Amount BY Supplier, Invoice Num)CASE WHEN RANK(Amount BY Supplier, Invoice Num) = 1 THEN Code END

[Deleted User]

SUM(Amount by Invouce Num, Supplier) - but not sure what the "code" infront is supposed to present. the MAX? Which would be weird. Also code 133 is in the first "bin" as well as the second.

Teresa Modesta

Hello Joel,

very sorry for the time in getting to you on this - my example was very simple the complexities on the real thing not so much.

Your "CASE WHEN RANK(Amount BY Supplier, Invoice Num) = 1 THEN Code END " - proved to be the logic I needed - I am still struggling to understand how when displaying at header level it manages to not show all of the distributions lines that have a null result - but it works so you gave me exactly what I asked for.

To Christian - the code is an accounting segment - sorry if I was less than totally clear - my English is not always what it should be - I was trying to say that I needed for each invoice the accounting segment that holds the greatest total value.

thank you all again,

Teresa X.

[Deleted User]

No problem it isn't about english but about query grain and aggregations. Many people stumble over issues with junk attributes which they add "just because" and then get bitten by insufficient models.