topic Case Selection based on a date interval in QlikView

Case Selection based on a date interval

Tue, 15 Dec 2009 15:09:56 GMT

Dear All,

I have a database of a change management system for an IT department. Each demand has an "Open_Date" and "Close_Date" fields. When a demand entered in to the system it does automatically have an "Open_date" value. When that demand solved by a technician this demand does automatically have "Close_Date" value. I want to show quantities of open and close cases by the selection of date. For example When I click Year and Month (i.e. november, 2009) I would like to see demand graphic between 1.11.2009 and 30.11.2009. How can I do that. Would you please offer a sample QV which has the same logic.

Thanks for your support.

Best Regards,

Asim Akin

Case Selection based on a date interval

prieper — Tue, 15 Dec 2009 17:20:20 GMT

Have you tried expressions like

=COUNT(IF(LEN(Close_Date) = 0, Open_Date))

This one should deliver all the open cases.

HTH
Peter

Case Selection based on a date interval

Tue, 15 Dec 2009 17:48:14 GMT

Hi Peter,

Thanks for your answer. Acutally I need a master calendar which is independent from Open_Date and Close_Date fields. when I choose the year and month from that calendar I would like to see the quantities of open cases and solved cases.

In my current design there are two different calendar one is for open_date and the other one for close_date. Please check the file attached.

Kind Regards,

/************************* ***************************/

I would like to ask another question. Are there any way to prompt "1257033600 and 1259539200" date interval instead of write to the below SQL statement.

/************************* ***************************/

SELECT

mdbadmin.chgcat.sym,

k.Adet1,

p.Adet2,

r.Adet3

FROM

mdbadmin.chgcat ,

(

SELECT

mdbadmin.chgcat.sym,

count(mdbadmin.chg.chg_ref_num) as Adet1

FROM

mdbadmin.chg

INNER JOIN mdbadmin.chgcat ON (mdbadmin.chg.category=mdbadmin.chgcat.code)

where

mdbadmin.chg.open_date between 1257033600 and 1259539200

and

mdbadmin.chgcat.sym not like 'DEPO%'

and

mdbadmin.chg.group_id is not null

group by mdbadmin.chgcat.sym

order by mdbadmin.chgcat.sym

)k,

(

SELECT

mdbadmin.chgcat.sym,

count(mdbadmin.chg.chg_ref_num) as Adet2

FROM

mdbadmin.chg

INNER JOIN mdbadmin.chgcat ON (mdbadmin.chg.category=mdbadmin.chgcat.code)

where

mdbadmin.chg.close_date between 1257033600 and 1259539200

and

mdbadmin.chgcat.sym not like 'DEPO%'

and

mdbadmin.chg.group_id is not null

group by mdbadmin.chgcat.sym

order by mdbadmin.chgcat.sym

)p,

(

SELECT

mdbadmin.chgcat.sym,

count(mdbadmin.chg.chg_ref_num) as Adet3

FROM

mdbadmin.chg

INNER JOIN mdbadmin.chgcat ON (mdbadmin.chg.category=mdbadmin.chgcat.code)

where

mdbadmin.chg.active_flag=1

and

mdbadmin.chgcat.sym not like 'DEPO%'

and

mdbadmin.chg.group_id is not null

group by mdbadmin.chgcat.sym

order by mdbadmin.chgcat.sym

where

mdbadmin.chgcat.sym = k.sym (+)

and

mdbadmin.chgcat.sym = p.sym (+)

and

mdbadmin.chgcat.sym = r.sym (+)

and

mdbadmin.chgcat.sym not like 'DEPO%'

and

( k.sym is not null or p.sym is not null or r.sym is not null )

order by mdbadmin.chgcat.sym

Case Selection based on a date interval

Sun, 14 Mar 2010 00:07:19 GMT

Hi Asim

I am totally new to Qlikview and am trying to see if it will fit my purpose. I have the exact same problem you describe above regarding open and closed support cases.

I would like to generate a graph that have independant time (dates) on the horizontal axis and indicate the number of open cases over the chosen period, based on whether or not the case is still open after being created on a certain date.

Have you found a solution?

Regards

Paul

Case Selection based on a date interval

Thu, 08 Apr 2010 11:22:18 GMT

The simple solution I found was to create a second table in Excel with a long list of dates up to deep into the future.

I then linked that table's Date column with all the other date entries in my main table. If I plot a graph of created cases vs. dates, all dates with no entries also show up in my graph so I can easily identify quite periods.

//Main cases table

LOAD DayCreated,

DayMod,

DayClosed

FROM

Cases.xlsx

//Dates table

LOAD Date as DayCreated,

Date as DayMod,

Date as DayClosed

FROM

Dates.xlsx