Set analysis to exclude a record when a date field is blank

[#1]

I have 3 fields

Actualdate

Currentdelinquent

scheduleddate

At the moment I have the following expression that excludes all of the records where the currentdelinquent field is = null

      If(not isnull([completeddelinquent]),actualdate )

This works but it also returns records where actual date is = to blank and I also want to exclude these records.

I think I need to use set analysis combined with the formula above but can't get it to work

[#2]

I now have this 

if(not isnull([completeddelinquent]) and Len(Trim(([actualdate]))) > 0,actualdate)

but I get the same result and the actual date where is was blank now returns null, I need to exclude the whole record if actual date is blank

[sasiparupudi1]

Please paste your script here

[jagan]

Hi,

Attach sample file so that it would be easier to provide the solution.

Regards,

Jagan.

[#5]

// To create view options in the dashboard

viewTable:
Load * Inline [
tableView
All Milestones
Future Milestones
5 Week Overview
Prev. Wk Cust Milestones
All Customer Milestones
All Milestones Delinquent
Current Milestones Delinquent
Completed Milestones Delinquent
Projected Future Delinquencies
];


// Project header data
PROJHEADER:
LOAD
PSPID as WBS,
Text(Left(PSPID,2) & '.' & Mid(PSPID,3,6)) As FormattedNumber,
left(PSPID,8) as WBSkey,
POST1 as projdes,
VBUKR as companycode,
WERKS as plant,
WWMKT as market

FROM

(qvd)

Where Not VBUKR = '0001';


// Required to bring in region
left join
Region:
LOAD CmpID as companycode,
Region
FROM

(biff, embedded labels, table is Company$);

// Project detail data
PROJDETAIL2:
LOAD PSPNR as projectno,
POSID as wbsno1,
//Text(Left(POSID,2) & '.' & Mid(POSID,3,6) & '.' & Mid(POSID,9,2) & '.' & Mid(POSID,11,2) & '.' & Mid(POSID,13,2) & '.' & Mid(POSID,15,2)) as FormattedNumber1,
Text(Left(POSID,2) & '.' & Mid(POSID,3,6) & If(Mid(POSID, 9,2) <> '0', '.' & Mid(POSID,9,2), '') & If(Mid(POSID, 11,2) <> '0', '.' & Mid(POSID,11,2), '') & If(Mid(POSID, 13,2) <> '0', '.' & Mid(POSID,13,2), '') & If(Mid(POSID, 15,2) <> '0', '.' & Mid(POSID,15,2), ''))as FormattedNumber1,
left(POSID,8) as WBSkey,
left(POSID,2) as ProjectID,
POST1 as wbsdesc,
OBJNR as objectno,
PSPHI as currprojno,
VERNA as projectmanager,
STUFE as projectlevel



FROM

(qvd)

Where not left(POSID,2)> '90';



// Exclusion of closed projects
Left Join (PROJDETAIL2) LOAD
OBJNR as objectno,
STAT AS Projectstatus,
'X' as IGNORE2
FROM

(qvd) where STAT = 'I0046';

left join (PROJDETAIL2) LOAD FormattedNumber1,
only('X') as IGNORE
resident PROJDETAIL2 where IGNORE2 = 'X' group by FormattedNumber1;



NoConcatenate
PROJDETAIL:
load * RESIDENT PROJDETAIL2
Where IGNORE <> 'X';

drop table PROJDETAIL2;


// Operation number to link WBS with Milestone number
AFVC:
LOAD AUFPL as operationno,
PROJN as projectno,
AUFPL+APLZL as opno


FROM

(qvd)

where PROJN > 0;

// Milestone data
left join
MLST:
LOAD AUFPL as operationno,
APLZL as counter,
AUFPL+APLZL as opno,
MLST_ZAEHL as milestoneno,
num (MLST_ZAEHL) as milestone,
MLSTN as usage,
TEDAT as scheduleddate,
LST_ACTDT as actualdate,
if((TEDAT>today()),TEDAT) as FutureMilestones,
if(TEDAT>Today()and TEDAT <Today()+35,TEDAT)as '5WeekOverview',
if(TEDAT>=WeekStart(today(),0,-1)-7 and TEDAT<=WeekStart(today(),0,-1),TEDAT)as PrevWk,
if(TEDAT < Today() and LST_ACTDT = '',TEDAT) as Alldelinquent,
if(TEDAT < Today() and LST_ACTDT = '' and TEDAT >today()-304,TEDAT) as Currentdelinquent


FROM

(qvd)

where MLSTN = 'N0003';


// Milestone customer baseline data
left join
LOAD ZZMLST_ZAEHL1 as milestone,
ZZMLST_TEDAT1 as customerbaseline

FROM

(qvd);

Calctable:

NoConcatenate

// Creation of additional calculated fields
load
customerbaseline,
if(actualdate > ('') and actualdate > customerbaseline and scheduleddate > customerbaseline and customerbaseline > '' and actualdate > (''),scheduleddate) as completeddelinquent,
if(actualdate = ('') and scheduleddate > customerbaseline, scheduleddate) as projectdelinquent
Resident AFVC;



// Milestone descriptions
MLTX:
LOAD MLTX_ZAEHL as milestoneno,
KTEXT as milestonedesc,
Right(KTEXT,6) as Cust,
if(Right(KTEXT,6)='(Cust)' or Right(KTEXT,6)='(cust)','Customer','Internal') as Type

FROM

(qvd);

[#6]

Hi

file added

[sasiparupudi1]

Since you want to exclude all blank or null actualdate values,could may be use

where len(trim(actualdate))>0;

after the resident load Resident AFVC

[#8]

Hi

Thanks, I need to deal with this in the expressions as I need all the records to create different views

[sasiparupudi1]

Hi

Try  changing the Companycode dimension to a calculated one with the condition

=if(actualdate<>'',companycode)

[sasiparupudi1]

Hi

Is this still an issue?

Sasi