topic Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES in QlikView

SUPRESS NULL VALUES BUT SHOW ZERO VALUES

wale_martins — Thu, 29 Mar 2018 12:23:46 GMT

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

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

olivierrobin — Thu, 29 Mar 2018 12:32:51 GMT

hello

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

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

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

wale_martins — Thu, 29 Mar 2018 12:45:16 GMT

Hi Olivier,

Supress Missing is already checked....

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

sergio0592 — Thu, 29 Mar 2018 12:51:37 GMT

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))

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

wale_martins — Thu, 29 Mar 2018 14:01:50 GMT

Hi Jean,

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

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

sergio0592 — Thu, 29 Mar 2018 14:15:38 GMT

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))

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

sasiparupudi1 — Thu, 29 Mar 2018 14:21:15 GMT

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))

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

wale_martins — Thu, 29 Mar 2018 14:29:12 GMT

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

Thanks

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

wale_martins — Thu, 29 Mar 2018 14:31:38 GMT

Hi Sasidhar,

There's something wrong in the expression...

Thanks for your time

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

sasiparupudi1 — Thu, 29 Mar 2018 14:36:32 GMT

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)

)

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

wale_martins — Thu, 29 Mar 2018 16:00:58 GMT

Hi Sasidhar,

It continues being something wrong.... sorry

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

vishsaggi — Thu, 29 Mar 2018 18:15:20 GMT

may be try this?

= 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))

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

wale_martins — Thu, 29 Mar 2018 20:04:49 GMT

It continues wrong... I don't know where the mistake it is...

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

vishsaggi — Thu, 29 Mar 2018 20:22:13 GMT

Try this

= 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))

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

wale_martins — Thu, 29 Mar 2018 20:29:27 GMT

Neither..... Don't worry I will try to do it in another way......

Thank you for your time

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

vishsaggi — Thu, 29 Mar 2018 20:36:50 GMT

Hmm. Would you mind sharing your app where you have this expression used and your expected output?

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

wale_martins — Thu, 29 Mar 2018 21:02:55 GMT

Hi Vishwarath

There's some confidential data in it... Let me prepare a new document to attach.

Thank you very much!

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

wale_martins — Thu, 29 Mar 2018 21:47:15 GMT

Hi Vishwarath

Take a look to the attached file. I can't reproduce the exact situation... But what I want is that in my straight table '>40' appear all the customers... even those with no visits, but not the first 40.

Thank you in advance.

Wale

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

vishsaggi — Fri, 30 Mar 2018 13:50:14 GMT

Sorry could not check last night. will look into it and get back.

Re: SUPRESS NULL VALUES BUT SHOW ZERO VALUES

wale_martins — Fri, 30 Mar 2018 16:55:29 GMT

Don't worry! Just tell me if you find a solution for this issue!

Thanks!