4 Replies Latest reply: May 1, 2015 8:35 AM by Neil Deeley RSS

    if Statement not removing rows from Pivot Table

    Neil Deeley

      Hi there,

       

      I'm trying to suppress rows in a pivot table that don't meet a criteria - in this case, if the row is not an empty string, produce a sum:

       

      sum({$ < Period-="", [CostHeader]-={""}, [Cost Service]-={""}, [Expense Element]-={""}>} [Amount])

       

      This works fine for my output:

       

      pic1.png

       

      The values show the total of my figures, and I`m now trying to hide these until a filter is selected. I've created an IF statement, but it sno longer suppressing my unwanted row, even when the filter is selected:

       

      if(

      Match([SummaryType], 'Actual', 'Budget', 'RollingForecast'),

      sum({$ < Period-="", [CostHeader]-={""}, [Cost Service]-={""}, [Expense Element]-={""}>} [Amount]))

       

      pic2.png

       

      pic3.png

      I guess it's an error in this set analysis - any idea what I am doing wrong?

       

      Thanks

      Neil