topic Re: Chart Expression in QlikView

Chart Expression

Mon, 28 Dec 2015 08:22:45 GMT

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.

Re: Chart Expression

Chanty4u — Mon, 28 Dec 2015 09:37:06 GMT

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

Re: Chart Expression

Anonymous — Mon, 28 Dec 2015 09:41:33 GMT

missing brackets in syntax..

may be like this:

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

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

Re: Chart Expression

tresB — Mon, 28 Dec 2015 09:43:22 GMT

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.

Re: Chart Expression

settu_periasamy — Mon, 28 Dec 2015 09:56:31 GMT

May be try this..

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

Re: Chart Expression

Mon, 28 Dec 2015 12:30:37 GMT

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

Re: Chart Expression

Tue, 29 Dec 2015 01:46:00 GMT

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.

Re: Chart Expression

Tue, 29 Dec 2015 01:56:19 GMT

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.

Re: Chart Expression

settu_periasamy — Tue, 29 Dec 2015 02:22:11 GMT

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#))

Re: Chart Expression

Tue, 29 Dec 2015 02:41:26 GMT

Hi Sureh,

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

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

Re: Chart Expression

Tue, 29 Dec 2015 02:49:45 GMT

Hi Settu,

I have got the first bar correct using :

count(DISTINCT{<POStatus={'200'}>}OMSPO#) based on this Table :

the challenge now is the 2nd bar, I need to count the OMSPO# , where theTransmdateF & StateDAteF are the Min date and the POStatus = '200 , and DayDiff is <= 2 days. Appreciate if you could help.

I tried this : But failed.

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

Thank you.

Re: Chart Expression

settu_periasamy — Tue, 29 Dec 2015 13:21:31 GMT

Hi,

Can you provide the sample data? Then, we can test and give the proper expression.

Just try this..

=Count(If(Aggr(min(StatusDateF), OMSPO#)-Aggr(min(TransmDateF), OMSPO#)>=2

and POStatus='200', OMSPO#))

Re: Chart Expression

Tue, 29 Dec 2015 14:11:23 GMT

Hi,

Pls find attach sample file. Thank you very much.

Regards,

Andros Goh

Re: Chart Expression

settu_periasamy — Tue, 29 Dec 2015 15:00:31 GMT

Hi Andros,

May be like Attached..

Re: Chart Expression

Tue, 29 Dec 2015 15:11:23 GMT

Hi,

I saw the attached QVW. But I need the solution on the bar chart.

1 bar showing the Total OMSPO# (which I already solved). The other bar which is the “KPI” should Count the no. of OMSPO# where the POStatus=’200’, MIN date for StatusDateF, Min date for TransmDate and DayDiff<=2.

My scripts for Expression (second bar Target2) was below but wrong. I need the correct Expression for the chart ! Thank you.

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

[cid:image002.jpg@01D1428E.022910D0]

Regards,

Andros Goh

Re: Chart Expression

manishchandra_j — Tue, 29 Dec 2015 15:32:08 GMT

Hi Andros,

Please try this for second expression

Count(

{

$(=Min({<POStatus={'200'}>}StatusDateF) - Min({<POStatus={'200'}>}TransmDateF))=2

}

OMSPO#)

Re: Chart Expression

Wed, 30 Dec 2015 01:36:00 GMT

Hi,

There is error on the comma : between ..” 2,OMSPO#)”

Count({<$(=Min({<POStatus={'200'}>}StatusDateF) - Min({<POStatus={'200'}>}TransmDateF))<=2,OMSPO#)

I think is because of the <= that caused it. I tried <={‘2} also failed.

Regards,

Andros Goh

Re: Chart Expression

manishchandra_j — Wed, 30 Dec 2015 14:53:06 GMT

Hi Andros,

I think it's copy-paste error, there is no comma between .” 2,OMSPO#)” I had put the expression in multiple lines to make it easy to understand.

Try below.

Count({<$(=Min({<POStatus={'200'}>}StatusDateF) - Min({<POStatus={'200'}>}TransmDateF))=2>} OMSPO#)

Re: Chart Expression

Thu, 31 Dec 2015 00:36:25 GMT

Hi,

Pls see below : there is error after the =>2}. Thank you.

Regards,

Andros Goh

Re: Chart Expression

manishchandra_j — Thu, 31 Dec 2015 10:23:23 GMT

Hi,

Don't worry about that red line, sometimes it gives you that kind of indication even if you expression if correct. use it ans see if you are getting the desired result. I tested this expression myself and it's working.