Pivot table with input filter, if vs set analysis

[olac]

Hi, 

We are working with input filters in two reports. 

In the first one, we have the data we want to filter in a column. The scenario here is invoices overdue, so it is a calculated number of days made in the script at load. Easy enough, we can here put it in a set analysis like: 

Sum({<DAYS_OVERDUE = {">=$(=vDaysOverdueInputFilter)"}>} [OPEN_AMOUNT])

The result then is that the pivot table excludes the rows that are below what is entered in the input filter - All good. 

Now I have a second report where they again want an input filter, but this time around, it filtered on a more complex calculation. A percentage calculation derived from total revenue divided by external revenue (Ebit sort of). Since this is done on a higher level up in the pivot table, I do not think I can put this back in the script and load step. 

For that reason, I think I also need to do an if statement in my expression rather than using a set analysis for the input filter.  

Something like: 

if
(sum(Revenue) /  sum(externalRevenue) >$(vInputVariable),
sum(Revenue) /  sum(externalRevenue)
,nul())

The pivot table correctly shows null where the threshold is below my filter. But it still shows the rows even if they are all nulls. 

So the if statement in my expression seems to be handled more front end than the set analysis? 

How can I get my pivot table with the if statement to hide rows with all null values, just as when I am using the filter in a set analysis?

Thanks

Not showing where all rows are null (set analysis):

Showing null values (if statement in the expression)

All in Qlik sense cloud

[theoat · Accepted solution]

Hello, try do uncheck "include null values" in "add ons" section.

Enjoy your Qlik.

Kind regards,
Théo ATRAGIE.

[ali_hijazi · Accepted solution]

in the properties of the pivot table, go to Add-Ons->Data Handling-> un check include zero values

[olac]

Yeap thats it, thanks!

[olac]

Yeap thats it, thanks!

[theoat]

Have a great day.