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

[bmensing]

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?

kr

[engishfaque]

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

[bmensing]

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

[sohailansari201]

That works for me. Thanks Ishfaque.