Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ndeeleysww
Creator
Creator

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
sunny_talwar

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
Creator
Creator
Author

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)

)


sunny_talwar

Would you be able to share a sample application?

ndeeleysww
Creator
Creator
Author

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.