In TDP workbook 5.0.6.95, trying to figure out how to add a summary filter on the AMT column in Data Area. I want to only show rows where Sum of AMT is not equal to zero. If I click the filter icon on Sum of AMT (see below), it prompts me to enter a range of values which won't work if I only want to see rows <> 0.
Debbie, I tried that but it’s not working. In the pivot below, I want to filter out all rows where Sum of AMT Total = 0. In the sample below all rows would
be filtered out except the row where Sum of AMT Total = 4640.06. I think filtering on AMT>0 or AMT<>0 won’t work because there are multiple rows from results being summarized in the pivot. I think the summary filter (screen shot from original post) may need
to be modified to allow operators = or <> in addition to the range filter.
Steve
I don't recognize the filter screen you show. Where did you invoke this from? Post screenshot, please of what you pressed to use that dialog.
Try adding AMT to the Filter Area and then add the condition there.
Tried that but it doesn’t eliminate any of the zero rows in the pivot. I think the filter needs be Sum of AMT<>0 instead of AMT<>0.
If you right click on the "Sum of X" in the "Data Area" and select "Move to Report Filter"
The "Sum of X" will be available as filter just click the filter icon of "Sum of X" in the "Filter Area"
You will need to add the X to the "Data Area" again from your selection of Items.
I tried what you suggested but it doesn’t seem to work, it’s still filtering on AMT not Sum of AMT so all the zero rows are still showing.
I have taken another stab at this. I created this very simple table. One item category has two rows of 0 values and the second category has one value and one 0 value.
The default of pivoting Item is this:
But you don't want to see any rows from the Apples category but you want to see all rows of Oranges. Correct? If I add a range filter on the Sum of Total column and say I only want sum values from 1 - 100000 it will exclude all Apples rows but include all Oranges rows. This seems to be what you are asking for.
Here is the filter I used. It if on the sum of total and the range excludes 0.
Debbie, your example is similar to my query, except I have both positive and negative totals in my pivot so using the single "show values from x to y" there isn't a way to filter out the zero rows. I would need to show values from -9999999 to -.01 and .01 to 9999999 but the filter only allows one range to be entered.
Mnnn....let me ponder on that a bit.
I don't see a way to do this currently. I entered QAT-15054 to see how we can add a complex filter to aggregated data values.