Function not displaying values in straight table

[andrea0901]

Hi All,

i have written a flag conditions:

If(Len(Trim(EKPO.MATNR)) = 0, 1, 0) as MATNR_Flag,
if(len(trim(PIR_KBETR))=0,1,0) as Price_Flag,

Basically i am checking if my EKPO.MATNR is blank, then i need how many are null and how many or not.

After this i have created a straight Table as below:

Source	Nulls in MATNR	Notnull- Matnr	Total Matnr	NullMatnr%	NotnullMatnr%	Nulls-Price	Notnulls - price	notnullprice%
ABC	6816	156484	163300	0.04173913	0.95826087	0	1234	100%
DEF	5677	1234	6911	0.82144407	0.178555925	0	345345	100%
GHI	78967	4567	83534	0.94532765	0.054672349	0	34345	100%
JKL	789	5667	6456	0.1222119	0.877788104	0	0	-
MNO	6785	678	7463	0.90915182	0.090848184	0	234234	100%

In Nulls in MTNR : i have written the code as 

=Count({$<MATNR_Flag = {'1'}>} EKPO.MATNR)

in Notnull - Matnr: i have written code as :

=Count({$<MATNR_Flag = {'0'}>} EKPO.MATNR)

In NullMatnr% column i have given this code and it is working:

=Count({$<MATNR_Flag = {'1'}>} EKPO.MATNR)/Count({$<MATNR_Flag =>} EKPO.MATNR) 

same for NotnullMatnr% : =Count({$<MATNR_Flag = {'0'}>} EKPO.MATNR)/Count({$<MATNR_Flag =>} EKPO.MATNR) 

But the same way i am wrtting the code for Nulls- Price and the rest of columns i am not getting the correct values.

its all showing 0 . 

Please help me out.

Regards

Andrea.

[anushree1]

Why are you passing the values in quotes like '0' and '1' within set , they are  defined 1 and 0 in the script right?

[andrea0901]

yes, i am taking in the front end as well. 

[sunny_talwar]

For counting nulls, why don't you just try this

Sum(Price_Flag)

[andrea0901]

Hi Sunny,

I tried sum(Price_Flag) it is counting all 1's and giving the values.

I need to count Null and Not null values and used the following code as below:

=Count({$<Price_Flag = {'1'}>} PIR.KBETR) but i am getting the all values as Zero

For Not null , i have written, =Count({$<Price_Flag = {'0'}>} PIR.KBETR)  for this expression i am gettign notnull values.

and then calculating the percentage as below:

Count({$<Price_Flag = {'1'}>} PIR.KBETR)/Count({$<Price_Flag =>} PIR.KBETR)  this is also  not working.

[sunny_talwar]

But isn't Price_Flag = 1 when PIR_KBETR is Null and equals 0 when PIR_KBETR is not null? If this is true Sum(Price_Flag) should equal to the number of places when PIR_KBETR is null, right?

[sunny_talwar · Accepted solution]

For example

PIR_KBETR,    Price_Flag

abc, 0

, 1

, 1

adgs, 0

Now, if you do Sum(Price_Flag) it should be 2 and not 4, right? Because Sum of 0, 1, 1, 0 = 2... count will be 4, but not sum

[andrea0901]

Hi sunny ,
Thanks a lot. It worked .