Null rows not getting suppressed in Pivot Table

Wed, 25 Nov 2020 16:16:04 GMT

As you can see in the screen, There are some months which have no data in it but are still displayed. I cant delete them in the raw data

as these months have some other associated data with it. How do i hide these months in below pivot table?

I tried using "conditional" in Expressions , Suppress null values checkbox in dimensions (with Trim in edit script) which didn't work for me.

Attaching the Table File + Qlikview file

Re: Null rows not getting suppressed in Pivot Table

sunny_talwar — Thu, 26 May 2016 10:58:19 GMT

I think you needed to restrict the columns with image in them:

Using Column position - > Column(2)

=If(Len(Trim(Column(2))) > 0,

if(sum([Unique Leads 1 Month])/sum([Active Contacts]) >=

Avg(TOTAL <Brand_Desc> Aggr(If(Rank(sum([Unique Leads 1 Month])/sum([Active Contacts])) <= 3,

sum([Unique Leads 1 Month])/sum([Active Contacts])), Brand_Desc, Month)),

'qmem://<bundled>/BuiltIn/led_g.png',

if(sum([Unique Leads 1 Month])/sum([Active Contacts]) <=

Avg(TOTAL <Brand_Desc> Aggr(If(Rank(-sum([Unique Leads 1 Month])/sum([Active Contacts])) <= 2,

sum([Unique Leads 1 Month])/sum([Active Contacts])), Brand_Desc, Month)),

'qmem://<bundled>/BuiltIn/led_r.png', 'qmem://<bundled>/BuiltIn/led_y.png')))

Using Column label - > [%]

=If(Len(Trim([%])) > 0,

if(sum([Unique Leads 1 Month])/sum([Active Contacts]) >=

Avg(TOTAL <Brand_Desc> Aggr(If(Rank(sum([Unique Leads 1 Month])/sum([Active Contacts])) <= 3,

sum([Unique Leads 1 Month])/sum([Active Contacts])), Brand_Desc, Month)),

'qmem://<bundled>/BuiltIn/led_g.png',

if(sum([Unique Leads 1 Month])/sum([Active Contacts]) <=

Avg(TOTAL <Brand_Desc> Aggr(If(Rank(-sum([Unique Leads 1 Month])/sum([Active Contacts])) <= 2,

sum([Unique Leads 1 Month])/sum([Active Contacts])), Brand_Desc, Month)),

'qmem://<bundled>/BuiltIn/led_r.png', 'qmem://<bundled>/BuiltIn/led_y.png')))

Thu, 26 May 2016 11:41:30 GMT

Not at all surprised seeing its you who resolved it so quickly! Thanks once again!