Code with the largest total value for Invoice Num, Supplier
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.
Answers
-
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
0 -
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?
0 -
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.
0 -
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?
0 -
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
0 -
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.
0 -
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.
0 -
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.
0
