Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am new to Qlik ... Need help to slove the below scenario
I have a table with Ticket no , created date, modified date, status (modify,submit,close) ,
Measure :
now I want to calculate how many ticket created based on time dimension ?
and I want to calculate how many tickets closed based on time dimension ?
Dimension : should be time drill down
table Looks like
Ticket no | createdate | changeDate | status |
1 | 27-Jan-14 | 28-Jan-14 | Modify |
1 | 27-Jan-14 | 29-Jan-14 | SUBMIT |
1 | 27-Jan-14 | 2-Feb-14 | CLOSED |
2 | 27-Jan-14 | 28-Jan-14 | Modify |
2 | 27-Jan-14 | 28-Jan-14 | SUBMIT |
3 | 2-Feb-14 | 2-Feb-14 | Modify |
3 | 2-Feb-14 | 8-Aug-14 | SUBMIT |
4 | 1-Mar-14 | 3-Mar-14 | Modify |
5 | 8-Aug-14 | 8-Aug-14 | Modify |
5 | 8-Aug-14 | 8-Oct-14 | SUBMIT |
6 | 9-Aug-14 | 9-Aug-14 | Modify |
6 | 9-Aug-14 | 8-Oct-14 | SUBMIT |
6 | 9-Aug-14 | 11-Nov-14 | CLOSED |
7 | 8-Oct-14 | 8-Oct-14 | Modify |
7 | 8-Oct-14 | 11-Nov-14 | SUBMIT |
8 | 11-Nov-14 | 11-Nov-14 | Modify |
8 | 11-Nov-14 | 11-Dec-14 | SUBMIT |
8 | 11-Nov-14 | 21-Dec-14 | CLOSED |
9 | 11-Dec-14 | 11-Dec-14 | Modify |
SET DateFormat = 'DD-MMM-YY';
TABLE:
LOAD [Ticket no],
createdate as Date,
'Created' as Status
FROM YourTable;
CONCATENATE (TABLE)
LOAD [Ticket no],
changeDate as Date,
status as Status
FROM YourTable;
Then create a master calendar from Date field:
Then use a calendar field as dimension, together with Status as second dimension and as expression
=Count(Distinct [Ticket no.])
SET DateFormat = 'DD-MMM-YY';
TABLE:
LOAD [Ticket no],
createdate as Date,
'Created' as Status
FROM YourTable;
CONCATENATE (TABLE)
LOAD [Ticket no],
changeDate as Date,
status as Status
FROM YourTable;
Then create a master calendar from Date field:
Then use a calendar field as dimension, together with Status as second dimension and as expression
=Count(Distinct [Ticket no.])
Hi
Look at my example
Just adding,
now I want to calculate how many ticket created based on time dimension ? swuehlalready suggested
and I want to calculate how many tickets closed based on time dimension ? for this do like this?
==Count({<status={'Closed'}>}Distinct [Ticket no.])
count(DISTINCT{<status={'CLOSED'}>}[Ticket no])
You are just loading data ...
Thanks swuehl
But If I do join with the master calendar and month as dimension I am not getting the right results.
see if I select JAN in the month dimension and the chart have two measures that number of tickets created and number of tickets closed in the same month I should get 2 and 1 . where as the month dimension is not showing any data.
could you please help me with that or
Not sure what your issue is here, could you post your QVW with your sample data loaded?
If I use my script from above with a YearMonth field added from Date (I have not created a master calender, but that's just an enhancement), I do get values for Jan:
YearMonth | Status | Count(Distinct [Ticket no]) |
Jan 2014 | Created | 2 |
Jan 2014 | Modify | 2 |
Jan 2014 | SUBMIT | 2 |
Feb 2014 | CLOSED | 1 |
Feb 2014 | Created | 1 |
Feb 2014 | Modify | 1 |
Mrz 2014 | Created | 1 |
Mrz 2014 | Modify | 1 |
Aug 2014 | Created | 2 |
Aug 2014 | Modify | 2 |
Aug 2014 | SUBMIT | 1 |
Okt 2014 | Created | 1 |
Okt 2014 | Modify | 1 |
Okt 2014 | SUBMIT | 2 |
Nov 2014 | CLOSED | 1 |
Nov 2014 | Created | 1 |
Nov 2014 | Modify | 1 |
Nov 2014 | SUBMIT | 1 |
Dez 2014 | CLOSED | 1 |
Dez 2014 | Created | 1 |
Dez 2014 | Modify | 1 |
Dez 2014 | SUBMIT | 1 |
There are no tickets closed in Jan 2014, because I assume that for closed tickets, you won't look at the createdate, but on changedate.
thanks a lot ... This worked for me with master calendar and few other changes...
Can I ask you one more favour ?? how to create a measure with the aging
example : I would to know the age between create to modify, modify to submit , submit to closed for each ticket..
and one more like need to the tickets which are in not closed but in submit , which are not in submit but in modify
Thanks a lot