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

[antose]

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)

[sunny_talwar]

Difficult to know without trying, but may be this:

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

[antose]

Thx! But this show all no of Artikelnr.

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

[sunny_talwar]

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

[antose]

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

[sunny_talwar · Accepted solution]

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)

[antose]

Nice! Big thanks Sunny! You saved my day!

Works like a charm!