Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
rbartley
Specialist
Specialist

Filtering where column total is null/0

Hi everyone,

I would like to hide the 2007 column in the pivot table below as there is no data for the selected countries (EU and Belgium), it is shown as 0 for both countries as I am using Sum(Value), but the all 2007 records are in fact null.  

Pivot 1.PNG

I tried to calculate the overall column total by using the expression: 

=Aggr(Sum(If(IsNull(Value),0,Value)),[Indicator Code],[Time Period])

 

Where the Indicator is represented by the CTX.. value and Time Period represents the year in the pivot section.  However, while this displays the correct result in the row for Belgium, it shows a null (-) in the EU row.  As you can see from the formula above, I have already tried to translate the null to zeros in order to cater for this, but it's not working as expected. 

 

Pivot Table.PNG

I wondered whether this was an issue with the pivot table, so I created a straight table, building the expression up step by step, but I still get the same result (nulls in the EU rows).  What I'm expecting to see is 72.44 on both EU and Belgium rows for 2005 and 18 on both rows for 2018 and 0 on both 2007 rows.  

Does anyone have any ideas?

Straight Table.PNG

 

Labels (1)
2 Replies
LReeve
Contributor III
Contributor III

Does it work by doing something like this for the measure:

=Sum({<Value = {"*"}>} Value)?
rbartley
Specialist
Specialist
Author

Hi Luke,

 

Thanks for replying.  Unfortunately, this doesn't sum up all the values at column level, it just shows the same value as Sum(Value).  And when I try to combine it with Aggr(), it reacts the same way as Sum(Value) too, so this does not solve my problem.