topic Re: Excluding Null-Values in Text-Box with Count() in QlikView

Excluding Null-Values in Text-Box with Count()

bmensing — Wed, 25 Nov 2020 16:16:04 GMT

Hello again,

in my app i am showing all orders with the order value, excluding null values in my Pivot. I am using the Option in the presentation tab.

While i am using the same Expression in a TextBox with a Count Command, the results are ofc different.

TextBox -> showing the whole number of orders including the ones without any value

Pivot -> excluding the orders with 0 value

how can i substract the number of orders with value 0 in a TextBox?

Re: Excluding Null-Values in Text-Box with Count()

engishfaque — Wed, 02 Nov 2016 15:01:57 GMT

Hi Bernd,

Here is the expression to exclude null values,

=Count ({<Orders -= {"=Null()"}>} Orders) //use exclusion operator "-="

Please note, write your Dimension or Field Name in set analysis from where you want to exclude Null values.

Kind regards,

Ishfaque Ahmed

Re: Excluding Null-Values in Text-Box with Count()

bmensing — Thu, 03 Nov 2016 07:19:50 GMT

Dear Ishfaque,

thanks for your help but unfortunately your solution didn't work, any other ideas? And there is something new i want know, how can i manage variables in my set Expression. Like, $(vOrderValue -= {"0"}

this is my whole Expression:

=sum(
aggr(
if ( BB_SperrZuord.archiviert = '0',
count (
{<
V_BelegKopf.BelegArt = {"U"},
V_BelegPos.Gesamtpreis -= {"=Null()"},
V_BelegKopf.AuftragsArt = {"10"},
V_BelegKopf.Firma = {"100", "160", "170", "180"},
//YV_KonfiKopf.GANr -= {"5*"},
BB_SperrZuord.Sperrgrund -= {"500", "996", "997", "998", "999"}
>}
distinct( V_BelegKopf.BelegNummer))* If(YV_KonfiKopf.GANr like '5*', 0,1),

Count (
{<
V_BelegKopf.BelegArt = {"U"},
V_BelegPos.Gesamtpreis -= {"=Null()"},
V_BelegKopf.AuftragsArt = {"10"},
//YV_KonfiKopf.GANr -= {"5*"},
V_BelegKopf.Firma = {"100", "160", "170", "180"}
>}
distinct(V_BelegKopf.BelegNummer))* If(YV_KonfiKopf.GANr like '5*', 0,1)

) /* Ende if() */

+

Count (
{<
V_BelegKopf.AuftragsArt = {"50"},
V_BelegPos.Gesamtpreis -= {"=Null()"}
>}
distinct(V_BelegKopf.BelegNummer))* If(YV_KonfiKopf.GANr like '5*', 0,1)

,Kalender.Datum, V_BelegKopf.BelegNummer) /* Ende aggr() */

)/* Ende first sum() */

best regards

Re: Excluding Null-Values in Text-Box with Count()

sohailansari201 — Thu, 17 May 2018 16:01:29 GMT

That works for me. Thanks Ishfaque.