Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Please paste your script here
Hi,
Attach sample file so that it would be easier to provide the solution.
Regards,
Jagan.
// 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
(
Where Not VBUKR = '0001';
// Required to bring in region
left join
Region:
LOAD CmpID as companycode,
Region
FROM
(
// 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
(
Where not left(POSID,2)> '90';
// Exclusion of closed projects
Left Join (PROJDETAIL2) LOAD
OBJNR as objectno,
STAT AS Projectstatus,
'X' as IGNORE2
FROM
(
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
(
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
(
where MLSTN = 'N0003';
// Milestone customer baseline data
left join
LOAD ZZMLST_ZAEHL1 as milestone,
ZZMLST_TEDAT1 as customerbaseline
FROM
(
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
(
Hi
file added
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
Hi
Thanks, I need to deal with this in the expressions as I need all the records to create different views
Hi
Try changing the Companycode dimension to a calculated one with the condition
=if(actualdate<>'',companycode)
Hi
Is this still an issue?
Sasi