topic Re: Count no of null in pivot table (as dim)? in QlikView

Count no of null in pivot table (as dim)?

antose — Wed, 25 Nov 2020 16:16:04 GMT

Hi!

I have a pivot table that show a top 20 (will be 100 in the real application)

The first dimension is following

If(aggr(rank(sum(Omsättning)),Artikelnr)<=100, Artikelnr, Null())

How do I add a dimension that count no of null values per region (20 – number of null)

Re: Count no of null in pivot table (as dim)?

sunny_talwar — Thu, 16 Feb 2017 11:59:13 GMT

Difficult to know without trying, but may be this:

Aggr(Sum(Aggr(If(Rank(Sum(Omsättning)) <= 100, 0, 1), Region, Artikelnr)), Region)

Re: Count no of null in pivot table (as dim)?

antose — Thu, 16 Feb 2017 12:20:07 GMT

Thx! But this show all no of Artikelnr.

I include a reduced test file if you have any idea.

Re: Count no of null in pivot table (as dim)?

sunny_talwar — Thu, 16 Feb 2017 12:33:05 GMT

It seems like they are missing and not just null. Is this true?

Re: Count no of null in pivot table (as dim)?

antose — Thu, 16 Feb 2017 12:41:20 GMT

Hmm! Correct! Is it possible to get this number at all without to generate 0 sales (omsättning) on all periods?

Re: Count no of null in pivot table (as dim)?

sunny_talwar — Thu, 16 Feb 2017 12:57:43 GMT

Try this:

=100-Aggr(Sum({<Artikelnr = {"=Rank(Sum(Omsättning)) <= 100"}>}TOTAL <Region> Aggr(If(Sum(Omsättning) > 0, 1, 0), Artikelnr, Region)), Artikelnr, Region)

Re: Count no of null in pivot table (as dim)?

antose — Thu, 16 Feb 2017 13:05:32 GMT

Nice! Big thanks Sunny! You saved my day!

Works like a charm!