Counting/Suming Null values

[rido1421]

Hi All

I would like to create an expression to

1. Count the number of line numbers that have no Job Numbers.

2.As you can see Job number 4 and 13 have no delivery dates so these are still outstanding how do we sum the Sales value of the  job numbers without Actual delivery dates to show our outstanding value ?

Line Number	Job Number	Sales Value	Planned Delivery	Actual Delivery
1001000	1	241.4650422	2015/12/10	2015/12/10
1001001	2	34.63654213	2015/12/11	2015/12/11
1001002	-	149.5357872	2015/12/12	2015/12/16
1001003	4	634.7705078	2015/12/13
1001004	5	742.9615042	2015/12/14	2015/12/14
1001005	6	400.2947361	2015/12/15	2015/12/15
1001006	7	618.182932	2015/12/16	2015/12/16
1001007	8	386.3584307	2015/12/17	2015/12/17
1001008	-	31.05663626	2015/12/18	2015/12/18
1001009	10	696.6233466	2015/12/19	2015/12/25
1001010	11	933.6470633	2015/12/20	2015/12/20
1001011	-	930.3053199	2015/12/21	2015/12/21
1001012	13	89.1849687	2015/12/22

Your assistance is appreciated.

Thanks

[Mark_Little]

HI,

1) Try =COUNT(IF(ISNULL([Job Number]) = -1, 1,0))

or use NullAsValue in script to add something to count in set analysis

2)Try=Sum(IF(ISNULL([Actual Delivery]) = -1,Sales Value))

Mark

[tamilarasu · Accepted solution]

Hi,

Use these tow expressions

1. Count({<[Job Number]={"=len(trim([Job Number]))=0"}>}[Job Number])

2. Sum({<[Actual Delivery]={"=len(trim([Actual Delivery]))=0"}>}[Sales Value])

Sample attached against your data.

[sunny_talwar]

I think for the first part you can also do NullCount([Job Number]) if those two places where we see '-' are true nulls.

[HirisH_V7]

Hi,

Check this,

=Sum({<[Job Number]-={"-"},[Actual Delivery]-={''}>}[Sales Value])

There by you will get the sum by exclusion of required Fields,

HTH,

PFA,

Hirish

[tamilarasu]

I guess, the values are blank and not hyphen. Lets wait for the reply from Rido.

[rido1421]

Thank you I have blanks and ' - ' so I had to use both concepts null and Len=0