2 Replies Latest reply: May 5, 2013 9:40 AM by Leandro B.

# Set Analysis question (grouping by date)

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

regards,

Leandro

PD: (ene = january, abr = april)

• ###### Re: Set Analysis question (grouping by date)

I think you can do it like this:

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

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

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

FROM

(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.])

• ###### Re: Set Analysis question (grouping by date)

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,