Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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]))
I guess it's an error in this set analysis - any idea what I am doing wrong?
Thanks
Neil
Try this:
=If(SubStringCount([SummaryType], 'Actual') = 1 or SubStringCount([SummaryType],'Budget') = 1 or SubStringCount([SummaryType], 'RollingForecast') = 1,
Sum({$ < Period-="", [CostHeader]-={""}, [Cost Service]-={""}, [Expense Element]-={""}>} [Amount]))
Hi there,
Sorry, no that's made no difference! I think its the other half of the IF statement, where the expression isn't equal, that's causing it to show the previously suppressed field (I've added this in but it's made no difference:
if(
Match([SummaryType], 'Actual', 'Budget', 'RollingForecast'),
sum({$ < Period-="", [CostHeader]-={""}, [Cost Service]-={""}, [Expense Element]-={""}>} [Amount]),
sum({$ < Period-="", [CostHeader]-={""}, [Cost Service]-={""}, [Expense Element]-={""}>} 0)
)
Would you be able to share a sample application?
I can't at the moment as the data is sensitive, but I'll add in some dummy data, pull out the page and post it for you.