I would like to remove the first row where the value of Sales are the same as the next row AND where the LEVEL is not the same. In this example, I would like rows 1 and 2 removed but not row 5 (same DIVISN). The reasoning behind this report is that there are row level filters applied based on the user and I want one report that will work for all levels of users. Using sections is not an option for this report as it all needs to be in one table.
This would be easy if there was a function opposite to previous() where it references the “following” row.
Just a question…would row 3 disappear too? I notice it has the same sales as row 1 and row 2. Also, how is this sorted; in other words, if one row remains with the same amount, how is the row that remains determined? Is it the lowest level row with an equivalent amount?
I’m taking a look at the possibility of either ranking or row level security. Ranking might work if you also have a number code for the level because that number can then be used as a measure in the rank function. (You could choose the bottom level for each sales amount.) Another possibility, if you have access to the universe, is to use a derived table.
I don’t think I was clear enough so thanks for the question. Row 3 needs to be retained. It should be the last one one with the same SALES value and a different LEVEL
Do you also have a level # stored in your database? You can include it on your graph, but hide the column by making it only 1 pixel wide and removing the borders. Then try using the rank function (from the dropdown):
Bottom 1 Based On: [Level #] For Each: [Sales Amount]
Calculation Mode Count
(BOB member since 2008-12-02)