Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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)
Difficult to know without trying, but may be this:
Aggr(Sum(Aggr(If(Rank(Sum(Omsättning)) <= 100, 0, 1), Region, Artikelnr)), Region)
Thx! But this show all no of Artikelnr.
I include a reduced test file if you have any idea.
It seems like they are missing and not just null. Is this true?
Hmm! Correct! Is it possible to get this number at all without to generate 0 sales (omsättning) on all periods?
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)
Nice! Big thanks Sunny! You saved my day!
Works like a charm!