Count Number of empty data in Columns in Pivot Table

[louisernould]

Hi Qlik Community !

Everything is in the title: I have a pivot table and I want to count the number of empty data in my pivot table.

In an excel file, I have something like that:

Ref	Week1	Week2	Week 3	Week 4
A	2	3		3
B	1	2	2	2
C			1	1
D		1	1	4
E	2	2	2	2
F	1			6

my code is:

CrossTable(week,test, 1)

LOAD

     Ref,

     Week1,

     Week2,

     Week3,

     Week4

FROM

[...]

I'm looking for a code to have a table like this one:

Ref	number of null
A	2
B	0
C	2
D	1
E	0
F	2

Does someone have an idea ? I tried with a loop but it wasn't good...

[#2 · Accepted solution]

Hi,

Have a look at the attached application,hope it helps!!

Kiruthi

[#3]

You can use Count(ALL DISTINCT week) - Count(week) keeping Ref as Dimension.