Categories
Suppressing all zero rows in OAC analyses without using Selection Steps?
I want to suppress rows in my analysis that show zeroes in all the wrong places. In the screenshot of the analysis's table below, I'm referring to rows showing the columns highlighted in red. Ideally, since every row in it contains only zeroes, the entire "Other Revenue" section should collapse.
Each of these three measure columns is a complicated calculation with a FILTER-USING that revolves around a presentation variable populated by a dashboard prompt; the prompt values appear as the month name at the top of each column.
My first obvious thought was to build an OR filter: filter where Aug-22 IS NOT 0, OR Jul-22 IS NOT 0, OR Jun-22 IS NOT 0.
This gives me the desired result of removing rows where all three values are zero, which is good, but has the nasty side effect of displaying a NULL in the rows where only one or two of the values is zero, like this:
So, my next thought was to create a new hidden column that is the sum of the three columns (I also tried throwing in the YTD amount in the rightmost column) and basing the filter on that. Even when I unhid the column and could see it displaying non-null non-zero numbers on the face the of the analysis, any filter I placed on that column treated it as if it were NULL, causing the entire analysis to short-circuit as if all filters had failed.
Finally, I settled on selection steps. Using the hidden column I had developed for the filter before, I tried both excluding the zero records and including only the non-zero records. While this method gave me the desired results for small samples - say, an Income Statement for a single company value - the hit to the performance was unacceptably bad. I went from a sub-30-second runtime to over five minutes on the small sample, and waited over twenty minutes with no results when running the report for a larger population.
Might there be another, more elegant solution to my dilemma?
Answers
-
Hi,
If you want to remove the rows only when the 3 columns are 0, instead of having OR conditions between your 3 conditions, change them to a AND condition.
That will remove the side effect of the NULL values in the one or two of these 3 columns. This will obviously work only if your analysis retrieve rows with all the values and you are using a table to display the values. If you are using a pivot, or a union, and these 3 values are separate rows, it is a different problem (but shouldn't be the case as you said it's a table in your analysis and not a pivot, and at the same time, the side effect you saw makes me says this is the case, but there aren't enough info about what your request looks like).
0 -
Thank you for responding so quickly, @Gianni Ceresa!
This is going to sound unnecessarily convoluted, but it's my own fault. I did it to myself.
The reason the "normal" filters weren't working, regardless of how I grouped them and whether I used AND or OR was because I was summing up groups of records that had no values matching the join conditions. This became obvious to me as soon as I put the period name back into the visualization. Suddenly, every record I had before became three records - one for each month!
Initially, I thought of some clever ways I might play with the FILTER-USING function in the analysis filters - for example, by putting an IN clause with multiple values in the USING clause instead of a single value - but it never got me any closer than I was before. That's because I was relying on the dashboard prompts to give me the month for each column, but the data for each column was also constrained by the joins I had set up in my semantic model between my time dimension and my GL balances table.
I felt like I was on the right track with the hidden column idea I had tried earlier, and that indeed turned out to be the winning move.
Using an AGGREGATE BY function to aggregate dollar amount by natural account gave me a non-zero number (shown above in green) if at least one of the three columns contained a non-zero number. However, if all three were zero, then the AGGREGATE BY column would also return a zero (shown above in red).
Filtering for cases where the AGGREGATE BY column (labeled "Marker2" in the screenshot above) gave me the solution I needed. Notice in the screenshot below that there are no "all zero" lines, and I now also have a couple of lines with one or two zeroes in them where I was showing NULL before.
1 -
I am having a similar issue but using Oracle analytics cloud , just curious how did you resolve this issue, i cant find an Aggregate By function, can you please share you solution
0 -
@User_Q7SHC , there isn't a "click here" solution to this kind of requirement.
It all depends on what your query (criteria) looks like, because that's the one you have to adapt to add filters excluding what you don't want. OP for example is retrieving data in rows, and want to filter sets of rows, this is what make it more challenging (not impossible, just require a longer expression) because it must work on that set instead of at a row level.
The answer only depends on your query.
"Aggregate By" isn't a function itself, but in an aggregation function (SUM, COUNT etc.) you can define by what attribute it should be aggregated, to force a different aggregation level than the natural level of the query as defined in criteria. If you didn't use it, you probably didn't need it (and it isn't part of the solution, it could be in your query or not).
0 -
@User_Q7SHC , there is an AGGREGATE function available in OAC; you can insert it into a formula … although it may adversely affect your analysis's performance.
In my particular situation, as you can see from the screenshots in my post above, I was breaking down vertically (Y-axis) by a six-digit account. The values across (X-axis) are by month, and those months are not necessarily consecutive; the user may select any three values for the months.
As a result, each row only has a value for one of the three columns. There is no value for a July balance in August, after all!
I can aggregate the monthly totals together for the account on the whole, though, and if that number is not zero (meaning at least one of the three months had a non-zero balance), then I will show that row. If all three monthly values for that account are zero, I'll simply filter out the row instead.
0
