Exclude Zeros

[#1]

Hi All,

Anybody knows how to exclude zero values for result of column(2)- column(1) which is 0.0  and -0.0 in straight or pivot table.

thanks in advance

[#2]

in presentation tab

Null symbol and Missing symbol option

see attachement

[#3]

if(column(2)- column(1)=0.0 or column(2)- column(1)='-0.0',' ',column(2)- column(1))

in presentation tab--

supress null value option already checked.

make sure if you have  more than one expression and if in a particular row in all column have null value then it automaticall supressed, if any one column not null value then it doesn't supressed.

also in dimension select field and then check maually supress null value option

SEE ATTACHEMENT

EXAMPLE--

IN SCRIPT--

LOAD * Inline [

id, A,B

1,2.2,2.3

2,1.7,1.7

3,2.9,3.6

4,4.5,4.5

5,8.6,5.5

];

THEN IN PIVOT-

DIMENSION1-  id

DIMENSION2- A

DIMENSION3- B

EXPRESSION-    A-B

[#4]

option is already selected, and suppress null is also selected,

Exp 1:Sum(Balance) and Exp 2:Sum(Amount)

Exp 3: Sum(Amount)- Sum(Balance)

if that is 0.0 and -0.0 then how to exclude them in front end or in back end..

[#5]

option is already selected, and suppress null is also selected,

Exp 1:Sum(Balance) and Exp 2:Sum(Amount)

Exp 3: Sum(Amount)- Sum(Balance)

if that is 0.0 and -0.0 then how to exclude them in front end or in back end..

[#6]

Hi There,

option is already selected, and suppress null is also selected,

Exp 1:Sum(Balance) and Exp 2:Sum(Amount)

Exp 3: Sum(Amount)- Sum(Balance)

if that is 0.0 and -0.0 then how to exclude them in front end or in back end..

[#7]

Hi There,

option is already selected, and suppress null is also selected,

Exp 1:Sum(Balance) and Exp 2:Sum(Amount)

Exp 3: Sum(Amount)- Sum(Balance)

if that is 0.0 and -0.0 then how to exclude them in front end or in back end..

[#8]

Hi There,

option is already selected, and suppress null is also selected,

Exp 1:Sum(Balance) and Exp 2:Sum(Amount)

Exp 3: Sum(Amount)- Sum(Balance)

if that is 0.0 and -0.0 then how to exclude them in front end or in back end..

[jonathandienst]

Hi

If you want to suppress the lines where Sum(Amount) and Sum(Balance) are equal, then use these expressions:

     Exp1: If(Round(Sum(Balance) - Sum(Amount)) <> 0, Sum(Balance), 0)

     Exp2: If(Round(Sum(Balance) - Sum(Amount)) <> 0, Sum(Amount), 0)

     Exp3: Round(Sum(Balance) - Sum(Amount))

And ensure that the Suppress Zero Values option is checked.

Jonathan

[#10]

Hi Jonathan,

I have the following code which gives me CMP_Date and Pre.

Wrkflow:
LOAD   Process_Code ,
Application_id   as   Application_Id ,
Workitem_Id ,
Activity_Code ,
Timestamp ( Txn_Date ) as   Txn_Date ,
State_Code ,
Assigned_User_Id ,
Assigned_Dept_Id ,
Actioned_User_Id ,
applymap ( 'Trays' , Activity_Code , null ()) as   Tray ,
applymap ( 'Trays' , Activity_Code , null ())& State_Code   as   Link ,
if ( State_Code = 'CMP' , timestamp ( Txn_Date ), null ()) as   [CMP_Date]

FROM

( txt , codepage   is   1252 , embedded   labels , delimiter   is   '\t' , msq ) ;

Wrkflow2:
Load   * , 1
Resident   Wrkflow
Order   by   Application_Id , CMP_Date , Tray , State_Code;
DROP   Table   Wrkflow;

Wrkflow3:
Load   * ,
CMP_Date - if ( State_Code = 'CMP'  ,( Previous ( CMP_Date )), '' ) as   diff ,
networkdays (( Previous ( CMP_Date )), CMP_Date ) as   diff2 ,

if ( State_Code = 'CMP'  ,( Previous ( CMP_Date )), '' ) as   pre
Resident   Wrkflow2;
DROP   Table   Wrkflow2;

I am using CMP_Date and Pre in the following expression to get working hours and it is working well if we see application by application but when it it looked by product type (which is Average) is not correct because there are zeros in each tray and the average is decreasing. How can I ignore zeros in average .

sum({<Tray={'NAPS CJA Automatic Check'}>}fabs(interval(((Networkdays(Date(pre,'DD/MM/YYYY hh:mm:ss'),Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

+

if(frac(date(pre))<num('$(vQuitTime)'),if(frac(date(pre))>num('$(vStartTime)'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(pre,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(pre),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)

+

if(frac(date(CMP_Date))>num('$(vStartTime)'),if(frac(date(CMP_Date))<num('$(vQuitTime)'),(Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss'))*24)/count(distinct Application_Id)+

sum({<Tray={'NAPS Contact Customer'}>}fabs(interval(((Networkdays(Date(pre,'DD/MM/YYYY hh:mm:ss'),Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

+

if(frac(date(pre))<num('$(vQuitTime)'),if(frac(date(pre))>num('$(vStartTime)'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(pre,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(pre),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)

+

if(frac(date(CMP_Date))>num('$(vStartTime)'),if(frac(date(CMP_Date))<num('$(vQuitTime)'),(Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss'))*24)/count(distinct Application_Id)+

sum({<Tray={'NAPS Print Documents'}>}fabs(interval(((Networkdays(Date(pre,'DD/MM/YYYY hh:mm:ss'),Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

+

if(frac(date(pre))<num('$(vQuitTime)'),if(frac(date(pre))>num('$(vStartTime)'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(pre,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(pre),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)

+

if(frac(date(CMP_Date))>num('$(vStartTime)'),if(frac(date(CMP_Date))<num('$(vQuitTime)'),(Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss'))*24)/count(distinct Application_Id)