Link to home
Create AccountLog in
Avatar of xllvr
xllvrFlag for United States of America

asked on

How can I hide rows in Pivot Tables where data value is zero Year 1 and >0 Year 2?

Happy New Year Experts!

I have attached a small sample of what I'm trying to achieve.  I have 2 years of data (2009 & 2010) and need to hide rows where there is no value in 2009 despite having value in 2010.  I have tried everything I know and so far nothing has worked since the pivot table treats both years equally in terms of filtering.

Please take a peek at the sample and let me know if there is a solution out there.  Many thanks! PivotHideBlanksTest.xlsx
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of xllvr


I'll have to see if I can make that work.  My source table is actually a different table that is extracted from Filemaker Pro.  It's got the same fields but all the dates are in a database friendly format going down one column.  Several more years are represented and sometimes, as you might imagine, 2009 isn't represented at all.  Only years where data exists are represented.  Let me play around with this for a moment and see what I can do.  I'll assign points in a short while!

Thank you!
Avatar of xllvr


Thanks very much for hanging around while I tried a few things.  Your solution works but since I added the formula to my original data table, calculation has slowed down considerably.  Anything I can do?  I haven't seen it this slow since I used hefty array formulas in Excel 2003.
If you have a lot of data in your source table, then I am afraid the performance will definitely suffer.

You could try performing that calculation on the database side, as databases generally do better with that sort of calculation as long as you have the right indexes and write a good SQL statement.

If your data source were Access of SQL Server I could help with that, but I have no experience in Filemaker.
Avatar of xllvr


It's not a lot of data at all...that's why this feels a bit mysterious to me.  It's only 3000 lines of data and the four pivot tables I have are all built off the original one.  The slow down didn't occur until I added the formula you provided.  Odd!
Well, that is a complicated thing: Excel has to do 54,000,000 comparisons to get the results.  (18,000 comparisons for each row, times 3,000 rows.)

I have no idea whether using structured references to a table results in faster, slow, or about the same performance than if straight range references are used.