Set Analysis question (grouping by date)

[#1]

Hi Community,

I need to generate a very simple report but I can't solve a issue. 

I have to show in a table (where the closing date is the dimension, and two expressions as columns: amount of closed tasks and amount of open tasks in this period.

Example:

Input Data:

Task No.	Request Date	Finish  Date
TS00001	dic-12	ene-13
TS00002	ene-13	ene-13
TS00003	ene-13	feb-13
TS00004	ene-13	ene-13
TS00005	ene-13	feb-13
TS00006	ene-13	mar-13
TS00007	feb-13	feb-13
TS00008	feb-13	mar-13
TS00009	feb-13	feb-13
TS00010	feb-13	mar-13
TS00011	feb-13	feb-13
TS00012	feb-13	mar-13
TS00013	mar-13	mar-13
TS00014	mar-13	mar-13
TS00015	mar-13	abr-13
TS00016	abr-13	abr-13

Expected result: (Request Date is the dimension)

Month	# of Task Opened	# of Task Closed
ene-13	5	3
feb-13	6	5
mar-13	3	6
abr-13	1	2

But when I take the closing date as dimension, I left out tasks to count as open for that period (because they are affected by selection), I try a lot of workarounds using set analysis but I failed to reach the solution

thanks in advance,

regards,

Leandro

PD: (ene = january, abr = april)

[swuehl · Accepted solution]

I think you can do it like this:

Convert your input cross table to a straight table using CROSSTABLE LOAD prefix:

CROSSTABLE (Type, Date, 1) LOAD

     [Task No.],

     date#([Request Date],'MMM-YY') as [Request],

     date#([Finish Date],'MMM-YY') as [Finish]

FROM

[http://community.qlik.com/thread/81292?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

[date#() function is only used to parse your request/finish dates correctly in as QV dates, thus allowing a proper sorting in the chart]

Then create a chart with dimenstion Date and two expressions:

=count({<Type = {'Request'} >} [Task No.])

=count({<Type = {'Finish'} >} [Task No.])

See also attached.

[#3]

Hi Swuehl

it works fine!

I had not evaluated using crosstabs, and clearly is the simplest option for what I needed to do.

thanks a lot,