Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
I think for the first part you can also do NullCount([Job Number]) if those two places where we see '-' are true nulls.
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
I guess, the values are blank and not hyphen. Lets wait for the reply from Rido.
Thank you I have blanks and ' - ' so I had to use both concepts null and Len=0