creating counting measure based with two date columns

[#1]

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

[swuehl · Accepted solution]

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:

The Master Calendar

Then use a calendar field as dimension, together with Status as second dimension and as expression

=Count(Distinct [Ticket no.])

[#3]

Hi

Look at my example

[Anonymous]

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

[#5]

count(DISTINCT{<status={'CLOSED'}>}[Ticket no])

[#6]

You are just loading data ...

[#7]

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

[swuehl]

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.

[#9]

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