topic Re: Counting/Suming Null values in QlikView

Counting/Suming Null values

rido1421 — Wed, 10 Feb 2016 08:26:25 GMT

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

Re: Counting/Suming Null values

Mark_Little — Wed, 10 Feb 2016 08:32:45 GMT

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

Re: Counting/Suming Null values

tamilarasu — Wed, 10 Feb 2016 08:35:18 GMT

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.

Re: Counting/Suming Null values

sunny_talwar — Wed, 10 Feb 2016 08:43:25 GMT

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

Re: Counting/Suming Null values

HirisH_V7 — Wed, 10 Feb 2016 09:06:47 GMT

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

Re: Counting/Suming Null values

tamilarasu — Wed, 10 Feb 2016 09:08:36 GMT

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

Re: Counting/Suming Null values

rido1421 — Wed, 10 Feb 2016 11:35:50 GMT

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