topic Re: Reported Date & Completed Date in a single list box in QlikView

Reported Date & Completed Date in a single list box

Sat, 07 Feb 2015 09:33:02 GMT

Hello All,

In an incident dump, we have incidents reported for the month of Jan 2015. [Reported Date Column]

Also the incidents closed for the month Jan 2015 which can be created in 2014 & Jan 2015 also.[Completed Date Column]

Now i need to have a list box of years 2014 2015 . selecting 2015 should show the Incidents Reported & Incident Closed in the Bar Chart.

Reported Date & Completed Date are two different columns.

How can i have the list box of Year which should take care of both [Incident Reported & Incident Closed].

Can you please suggest me. Appreciate your valued response.

Thanks,

Sasikumar

Re: Reported Date & Completed Date in a single list box

maxgro — Sat, 07 Feb 2015 10:35:56 GMT

see attachment

Re: Reported Date & Completed Date in a single list box

Sat, 07 Feb 2015 10:48:32 GMT

Hello Massimo,

I have a personal edition of QlikView, so cant able to open the qvw file. Can you provide me the code. Thanks in Advance.

Re: Reported Date & Completed Date in a single list box

maxgro — Sat, 07 Feb 2015 11:54:15 GMT

you can create a new table (y2) with both open and close year

SCRIPT:

load *, rowno() as id inline [

open, close, val

2014,2014,1

2014,2014,2

2014,2015,3

2013,2014,4

2013,2015,5

2015,2015,6

];

y2:

load id, open as year2 Resident t;

load id, close as year2 Resident t;

Re: Reported Date & Completed Date in a single list box

Sat, 07 Feb 2015 12:08:48 GMT

Hello,

My Edit Script:

SR:

LOAD [SR Number],

Title,

Category,

Status,

Impact,

Priority,

Department,

Requestor,

[Approved by],

Team,

Application,

[Team Manager],

[Assigned to],

VP,

[Created by],

Requested,

Approved,

Initiated,

Completed,

Year(Approved) as YearOpen,

Month(Approved) as MonthOpen,

Year(Completed) as YearClose,

Month(Completed) as MonthClose

FROM Table;

-- Here you can see Approved column as YearOpen, MonthOpen

Completed Column as YearClose, MonthClose.

The Open is correct which is 73.

But the closed count should be 124

Steps used to create

1) Dimension - MonthOpen

2)Expression- For Open

Sum({<[Category]-={Support},Status = {'Accepted','Completed','In Progress','On Hold','Pending AD Approval'}>}[MonthOpen])

3)Expression - For Closed,

Sum({<[Category]-={Support},Status = {'Accepted','Completed','In Progress','On Hold','Pending AD Approval'}>}[MonthClose])

Please suggest me. Thanks in Advance

Re: Reported Date & Completed Date in a single list box

Sat, 07 Feb 2015 15:00:30 GMT

Here you handle only for Close. But selected the year 2015, then close should be 5 and open should be 1

Year 2014, then close - 4 open- 4

Year 2013 then close - 0 open 4

Is it possible? Can you please help me, this is urgent.

Thanks in Advance.

SasikumarR