Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a requirement where I need to display the no.
of created tkts and no.of resolved tkst by each month.
here is the input table,
Ticket_number | created_on | Resoved_date |
INC3522866 | 2014/02/28 23:51:21 | 2014/03/04 15:34:30 |
INC3522836 | 2014/02/28 23:46:22 | 2014/03/03 14:48:11 |
INC3522821 | 2014/02/28 23:44:48 | 2014/03/03 14:29:12 |
INC3522804 | 2014/02/28 23:43:00 | 2014/03/07 18:07:31 |
INC3522781 | 2014/02/28 23:40:52 | 2014/03/04 16:16:06 |
INC3522766 | 2014/02/28 23:38:43 | 2014/03/04 15:50:04 |
INC3522752 | 2014/02/28 23:37:19 | 2014/03/04 15:33:15 |
INC3522737 | 2014/02/28 23:35:32 | 2014/03/11 15:10:46 |
INC3522715 | 2014/02/28 23:33:05 | 2014/03/04 15:27:51 |
INC3522696 | 2014/02/28 23:31:09 | 2014/03/04 15:50:42 |
INC3522682 | 2014/02/28 23:29:09 | 2014/03/04 15:37:41 |
INC3522625 | 2014/02/28 23:26:18 | 2014/03/04 15:45:14 |
INC3522587 | 2014/02/28 23:20:06 | 2014/03/04 15:59:26 |
INC3522560 | 2014/02/28 23:17:02 | 2014/03/04 15:53:35 |
INC3500608 | 2014/02/26 20:00:52 | 2014/03/05 21:06:22 |
INC3490435 | 2014/02/25 21:04:53 | 2014/03/15 07:31:25 |
INC3486790 | 2014/02/25 15:42:27 | 2014/03/07 21:53:58 |
INC3450140 | 2014/02/20 01:50:49 | 2014/02/27 15:18:33 |
INC3376969 | 2014/02/10 23:09:27 | 2014/02/12 15:36:59 |
INC3374649 | 2014/02/10 20:23:54 | 2014/02/14 00:45:58 |
INC3309028 | 2014/01/31 15:47:10 | 2014/02/06 19:34:13 |
INC3241280 | 2014/01/23 03:45:29 | 2014/01/29 22:21:30 |
INC3165238 | 2014/01/13 17:15:26 | 2014/01/13 17:21:06 |
INC3107435 | 2014/01/03 22:11:59 | 2014/01/09 14:36:37 |
My output look like this:
Month | expr1(count.of created tickets) | expr2(count.of reolved tickets) |
---|---|---|
jan | 4 | 3 |
feb | 20 | 4 |
mar | 0 | 17 |
I am attached sample qvw,I am able to get the expr1 values but..expr2 getting wrong values.
Thanks
Create below script....
=============================================================
T1:
LOAD Ticket_number,
TimeStamp(TimeStamp#(created_on,'YYYY/MM/DD hh:mm:ss')) as Created,
TimeStamp(TimeStamp#(Resoved_date,'YYYY/MM/DD hh:mm:ss')) as Resolved
FROM
[http://community.qlik.com/thread/133327]
(html, codepage is 1252, embedded labels, table is @1);
T2:
Mapping Load
Ticket_number,
Created
Resident T1;
T3:
Mapping Load
Ticket_number,
Resolved
Resident T1;
LinkedDate:
Load
Ticket_number,
'Created' as DateFlag,
ApplyMap('T2',Ticket_number,Null()) as CommonDate
Resident T1;
Concatenate
Load
Ticket_number,
'Resolved' as DateFlag,
ApplyMap('T3',Ticket_number,Null()) as CommonDate
Resident T1;
Join
Load
Ticket_number,
CommonDate,
Month(CommonDate) as Month
Resident LinkedDate;
========================================
Now Create a Straight Table
Dimension
Month
Expression
COUNT({<DateFlag = {'Created'}>}CommonDate)
COUNT({<DateFlag = {'Resolved'}>}CommonDate)
you might implement a canonical date calendar:
http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date
I do not see anything wrong.
What do you expect to see?
Greetings
You should ideally create a master calendar in the script to deal with such cases. See: master-calendar
and
Create below script....
=============================================================
T1:
LOAD Ticket_number,
TimeStamp(TimeStamp#(created_on,'YYYY/MM/DD hh:mm:ss')) as Created,
TimeStamp(TimeStamp#(Resoved_date,'YYYY/MM/DD hh:mm:ss')) as Resolved
FROM
[http://community.qlik.com/thread/133327]
(html, codepage is 1252, embedded labels, table is @1);
T2:
Mapping Load
Ticket_number,
Created
Resident T1;
T3:
Mapping Load
Ticket_number,
Resolved
Resident T1;
LinkedDate:
Load
Ticket_number,
'Created' as DateFlag,
ApplyMap('T2',Ticket_number,Null()) as CommonDate
Resident T1;
Concatenate
Load
Ticket_number,
'Resolved' as DateFlag,
ApplyMap('T3',Ticket_number,Null()) as CommonDate
Resident T1;
Join
Load
Ticket_number,
CommonDate,
Month(CommonDate) as Month
Resident LinkedDate;
========================================
Now Create a Straight Table
Dimension
Month
Expression
COUNT({<DateFlag = {'Created'}>}CommonDate)
COUNT({<DateFlag = {'Resolved'}>}CommonDate)
Hi Rajesh,
Try this script:
Temp:
LOAD Ticket_number,
Month(created_on) as Month1,
Month(Resoved_date) as Month2
FROM
(biff, embedded labels, table is [Sheet1$]);
load Month1 as Month,
count(Ticket_number) as Created_count Resident
Temp Group by Month1;
load Month2 as Month,
count(Ticket_number) as Resolved_count Resident
Temp Group by Month1;
and use the same month in the chart you will the solution, i am not able to reload the file.
Like this.
Regards
ASHFAQ
Thanku all...looks all your solutions are fine.
Thanks
Close the thread by selecting appropriate answer...
Test:
LOAD Ticket_number,
created_on,
Resoved_date
FROM
Harsha.xlsx
(ooxml, embedded labels, table is Sheet1);
Bridgetable:
Load
Ticket_number,
created_on as Date,
Monthname(created_on) as Month,
'CreatedOn' as DataType
Resident Test;
Concatenate (Bridgetable)
Load
Ticket_number,
Resoved_date as Date,
Monthname(Resoved_date) as Month,
'ResovedOn' as DataType
Resident Test;
Expression:
=count({<DataType = {'CreatedOn'}>}Ticket_number)
=count({<DataType = {'ResovedOn'}>}Ticket_number)