SUPRESS NULL VALUES BUT SHOW ZERO VALUES

[wale_martins]

Hi there,

I've been fighting for a long, trying to supress "null values" in a rank expression when "supress Zero-values" option is unchecked, but there's no way...

Anybody have an idea...

That's my expression:

IF(RANK(TOTAL COUNT({<AÑO={$(=(vYearSelect))}>}DISTINCT ID_VISITA),4)>40,

    COUNT({<AÑO={$(=(vYearSelect))}>}DISTINCT ID_VISITA))

In the attached file, you can see that the first 40 values appear as null, but appear.... the thing is that I want don't want to show them but I want to show the last 5 values with the 0 result. So, that's why I have unchecked the "supress Zero-values" option.

Thank you in advance

Wale

[olivierrobin]

hello

i would rather it is missing values (no visits for this dimension)

so i would check "suppress missing values" in presentation tab

[wale_martins]

Hi Olivier,

Supress Missing is already checked....

[sergio0592]

Maybe you can try with :

IF(RANK(TOTAL COUNT({<AÑO={$(=(vYearSelect))}>}DISTINCT ID_VISITA),4)>40,

COUNT({<AÑO={$(=(vYearSelect))},  NOMBRE_EMPRESA={"=COUNT({<AÑO={$(=(vYearSelect))}>} DISTINCT ID_VISITA>=1"}>}DISTINCT ID_VISITA))

[wale_martins]

Hi Jean,

It doesn't work..... thank you for your time

[sergio0592]

Ok, and if you try:

IF(RANK(TOTAL COUNT({<AÑO={$(=(vYearSelect))}>}DISTINCT ID_VISITA),4)>40,

COUNT({<AÑO={$(=(vYearSelect))},  NOMBRE_EMPRESA={"=Not IsNull(COUNT({<AÑO={$(=(vYearSelect))}>} DISTINCT ID_VISITA)"}>}DISTINCT ID_VISITA))

[sasiparupudi1]

Try

IF(RANK(TOTAL COUNT({<AÑO={$(=(vYearSelect))}>}DISTINCT ID_VISITA),4)>40,

    COUNT({<ID_VISTA={'=RANK(TOTAL COUNT({<AÑO={$(=(vYearSelect))}>}DISTINCT ID_VISITA),4)>40'}AÑO={$(=(vYearSelect))}>}DISTINCT ID_VISITA))

[wale_martins]

No way.... null values continue appearing and the rest turn into 0...

Thanks

[wale_martins]

Hi Sasidhar,

There's something wrong in the expression...

Thanks for your time

[sasiparupudi1]

I missed a comma

IF(RANK(TOTAL COUNT({<AÑO={$(=(vYearSelect))}>}DISTINCT ID_VISITA),4)>40,

COUNT(

{<ID_VISTA={'=RANK(TOTAL COUNT({<AÑO={$(=(vYearSelect))}>}DISTINCT ID_VISITA),4)>40'},

AÑO={$(=(vYearSelect))}>}

DISTINCT ID_VISITA)

)

)