Chart Expression

[#1]

Hi,

In a Bar Chart, I have first bar as Total PO NO (count(DISTINCT {<POStatus={'200'}>}  PO#) ,

in the second bar to compare against the first. my  logic is : 

count({<date(StatusDateF) - date(TransmDateF>='2',POStatus='200'>}),OMSPO#) , but failed.

I want to count the No. of PO where its POStatus is ='200' and where the StatusDateF - TransmDateF is equal or less than 2 days.

Can someone pls help.

Thank you.

[Chanty4u]

count({<date(StatusDateF) - date(TransmDateF>={'2'},POStatus={'200}'>}),OMSPO#)

[Anonymous]

missing brackets in syntax..

may be like this:

=count({<date(StatusDateF) - date(TransmDateF)>='2',POStatus={'200'}>} OMSPO#)

or

IF((date(StatusDateF) - date(TransmDateF))>='2' and POStatus='200', count(OMSPO#))

[tresesco]

Your set analysis syntax is wrong. You can't use anything except for a field (calculated in the script) on the LHS of set expression. I.e. ,   exp={Value} is invalid, it would be like - field={value/exp}. Try to share your sample qvw for specific and contexual help.

[settu_periasamy]

May be try this..

=count(if((StatusDateF-TransmDateF)>=2 and POStatus=200,OMSPO#))

[#6]

Hi,

This reply seems to be correct. If use on the secondary bar.

=count(if((StatusDateF-TransmDateF)>=2 and POStatus=200,OMSPO#))

But I realized I made a mistake in the first bar : PO count =count({<POStatus={'200'}>}OMSPO#).

My PO count should count  only the Min date of the OMSPO# and not count the OMSPO# on the second date (the same date). In the table below, I should have only 6 and not 8.

Please help. Thank you.

OMSPO#	POStatus	TransmDateF	StatusDateF	StatusDate	TransmDate	DayDiff	Target
000625	200	04/12/2015	09/12/2015	09/12/2015	04/12/2015	5	Y
000625	200	04/12/2015	09/12/2015	10/12/2015	04/12/2015	5	Y
000626	200	01/12/2015	07/12/2015	07/12/2015	01/12/2015	6	Y
000626	200	01/12/2015	07/12/2015	14/12/2015	01/12/2015	6	Y
00005638	200	04/12/2015	08/12/2015	08/12/2015	04/12/2015	4	Y
000012761	200	02/12/2015	04/12/2015	04/12/2015	02/12/2015	2	Y
00012762	200	02/12/2015	07/12/2015	07/12/2015	02/12/2015	5	Y
000012763	200	02/12/2015	07/12/2015	07/12/2015	02/12/2015	5	Y

[#7]

Hi Settu,

The result is 1 which is wrong. Below is my full script and I hope you can help me. As I am new and struggling with it.

[Shipment]:
load
distinct SubField(primaryReference, '-', 1) as OMSPO#, //Remove record with '-',
//orderNumber,
fileNumber,
serviceLevel as TransportMode,
departmentCode as Department,
creationEmployeeName as LoginUser,
statusCode as POStatus
FROM [Shipments.qvd] (qvd)
WHERE departmentCode like 'sg*';

left join // [Bookconf]:
Load  
//Date#(trackDate&chr(32)&trackTime,'DD/MM/YYYY hh:mm:ss') as StatusDate,
Date(trackDate) as StatusDate,
fileNumber, trackCode,  loggedBy  as POConfirmBy
FROM [TrackTrace.qvd] (qvd)
WHERE trackCode = 'BOOKCONF' and trackDate >= '2014-05-31';

left join //[EDIPO]:
load
fileNumber,
//Date#(trackDate&chr(32)&trackTime,'DD/MM/YYYY hh:mm:ss') as TransmDate,
   Date(trackDate) as TransmDate,
Date(trackDate) as Date,
Year(trackDate) as TransmYear,
Month(trackDate) as TransmMonth,
'Q' & Ceil(Month(trackDate) /3) AS Quarter
FROM [TrackTrace.qvd] (qvd)
WHERE trackCode = 'EDIPO'and trackDate >= '2014-05-31';


[CustAdd]:
Load   fileNumber, relationSearchname as Customer 
FROM [FileAdd.qvd] (qvd)
WHERE addressRole = '4';
left join // [FileRefs]:
Load   fileNumber,referenceText  as LOS
FROM [FileRef.qvd](qvd)
WHERE referenceCode = '2003';


[Status2]:
load OMSPO#,
//FirstValue(TransmDate)as TransmDateF,
// FirstValue(StatusDate)as StatusDateF
MIN(date(TransmDate)) as TransmDateF ,
MIN(date(StatusDate)) as StatusDateF
resident [Shipment]
WHERE POStatus = '200'
Group by OMSPO#
Order by StatusDate,TransmDate;

[Target]:
load date(StatusDateF) - date(TransmDateF) as DayDiff,
if (date(StatusDateF) - date(TransmDateF) >= '2', 'Y','N') as Target
Resident [Status2]

The Expression for first bar :

count(DISTINCT{<POStatus={'200'}>}OMSPO#)  = 526 which is correct.

second bard : Aggr (count (Distinct {<POStatus={'200'},StatusDate={"$(=Date (Min (StatusDate)))"}>}OMSPO#),OMSPO#,StatusDate)The Result is 1 which is wrong. Appreciate your help. Thank you.

[#8]

Hi Tresesco,

the script as follow :

[Shipment]:
load
distinct SubField(primaryReference, '-', 1) as OMSPO#, //Remove record with '-',
//orderNumber,
fileNumber,
serviceLevel as TransportMode,
departmentCode as Department,
creationEmployeeName as LoginUser,
statusCode as POStatus
FROM [Shipments.qvd] (qvd)
WHERE departmentCode like 'sg*';

left join // [Bookconf]:
Load  
//Date#(trackDate&chr(32)&trackTime,'DD/MM/YYYY hh:mm:ss') as StatusDate,
Date(trackDate) as StatusDate,
fileNumber, trackCode,  loggedBy  as POConfirmBy
FROM [TrackTrace.qvd] (qvd)
WHERE trackCode = 'BOOKCONF' and trackDate >= '2014-05-31';

left join //[EDIPO]:
load
fileNumber,
//Date#(trackDate&chr(32)&trackTime,'DD/MM/YYYY hh:mm:ss') as TransmDate,
   Date(trackDate) as TransmDate,
Date(trackDate) as Date,
Year(trackDate) as TransmYear,
Month(trackDate) as TransmMonth,
'Q' & Ceil(Month(trackDate) /3) AS Quarter
FROM [TrackTrace.qvd] (qvd)
WHERE trackCode = 'EDIPO'and trackDate >= '2014-05-31';


[CustAdd]:
Load   fileNumber, relationSearchname as Customer 
FROM [FileAdd.qvd] (qvd)
WHERE addressRole = '4';
left join // [FileRefs]:
Load   fileNumber,referenceText  as LOS
FROM [FileRef.qvd](qvd)
WHERE referenceCode = '2003';


//[Status]:
//Load 
// OMSPO#,
// FirstValue(StatusDate)as StatusDateF,
// FirstValue(TransmDate)as TransmDateF
// resident [Shipment]
// WHERE POStatus = '200'
//Group by OMSPO#
//Order by StatusDate,Transmdate;


[Status2]:
load OMSPO#,
//FirstValue(TransmDate)as TransmDateF,
// FirstValue(StatusDate)as StatusDateF
MIN(date(TransmDate)) as TransmDateF ,
MIN(date(StatusDate)) as StatusDateF
resident [Shipment]
WHERE POStatus = '200'
Group by OMSPO#
Order by StatusDate,TransmDate;

[Target]:
load date(StatusDateF) - date(TransmDateF) as DayDiff,
if (date(StatusDateF) - date(TransmDateF) >= '2', 'Y','N') as Target
Resident [Status2]

the Expression for first bar :

count(DISTINCT{<POStatus={'200'}>}OMSPO#)

For the Second bar, I need to get the count of all OMSPO# where the StatusDate and Transmdate is the first (Min) date and where the POStatus = '200'. Thank you very much.

[settu_periasamy]

Hi,

May be try this..

=Count(If(Aggr(min({<POStatus={'200'}>}StatusDate), OMSPO#) = StatusDate, OMSPO#))

Or, this one

=Count(Aggr(Min({<POStatus={'200'}>}StatusDate),OMSPO#))

[#10]

Hi Sureh,

Error : somewhere in this statement : (TransmDateF>={'2'},)

count({<date(StatusDateF) - date(TransmDateF>={'2'},POStatus={'200}'>}),OMSPO#)