Announcements
cancel
Showing results for
Did you mean:
Partner - Creator II

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

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)

1 Solution

Accepted Solutions
MVP

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)

6 Replies
MVP

Difficult to know without trying, but may be this:

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

Partner - Creator II
Author

Thx! But this show all no of Artikelnr.

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

MVP

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

Partner - Creator II
Author

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

MVP

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)

Partner - Creator II
Author

Nice! Big thanks Sunny! You saved my day!

Works like a charm!

Community Browser