Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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.
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.
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,