Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ndeeleysww
Contributor

if Statement not removing rows from Pivot Table

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

4 Replies

Re: if Statement not removing rows from Pivot Table

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]))

ndeeleysww
Contributor

Re: if Statement not removing rows from Pivot Table

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)

)


Re: if Statement not removing rows from Pivot Table

Would you be able to share a sample application?

ndeeleysww
Contributor

Re: if Statement not removing rows from Pivot Table

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.

Community Browser