Pivot table with input filter, if vs set analysis

olac — Wed, 06 Mar 2024 13:26:34 GMT

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

Re: Pivot table with input filter, if vs set analysis

theoat — Wed, 06 Mar 2024 14:19:03 GMT

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

Enjoy your Qlik.

Kind regards,
Théo ATRAGIE.

Re: Pivot table with input filter, if vs set analysis

ali_hijazi — Wed, 06 Mar 2024 14:22:19 GMT

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

Re: Pivot table with input filter, if vs set analysis

olac — Wed, 06 Mar 2024 14:38:42 GMT

Yeap thats it, thanks!

Re: Pivot table with input filter, if vs set analysis

olac — Wed, 06 Mar 2024 14:39:41 GMT

Yeap thats it, thanks!

Re: Pivot table with input filter, if vs set analysis

theoat — Wed, 06 Mar 2024 14:40:52 GMT

Have a great day.

topic Re: Pivot table with input filter, if vs set analysis in App Development

Pivot table with input filter, if vs set analysis

Re: Pivot table with input filter, if vs set analysis

Re: Pivot table with input filter, if vs set analysis

Re: Pivot table with input filter, if vs set analysis

Re: Pivot table with input filter, if vs set analysis

Re: Pivot table with input filter, if vs set analysis