Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

creating counting measure based with two date columns

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 nocreatedatechangeDatestatus
127-Jan-1428-Jan-14Modify
127-Jan-1429-Jan-14SUBMIT
127-Jan-142-Feb-14CLOSED
227-Jan-1428-Jan-14Modify
227-Jan-1428-Jan-14SUBMIT
32-Feb-142-Feb-14Modify
32-Feb-148-Aug-14SUBMIT
41-Mar-143-Mar-14Modify
58-Aug-148-Aug-14Modify
58-Aug-148-Oct-14SUBMIT
69-Aug-149-Aug-14Modify
69-Aug-148-Oct-14SUBMIT
69-Aug-1411-Nov-14CLOSED
78-Oct-148-Oct-14Modify
78-Oct-1411-Nov-14SUBMIT
811-Nov-1411-Nov-14Modify
811-Nov-1411-Dec-14SUBMIT
811-Nov-1421-Dec-14CLOSED
911-Dec-1411-Dec-14Modify
Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: creating counting measure based with two date columns

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

8 Replies
MVP
MVP

Re: creating counting measure based with two date columns

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

Not applicable

Re: creating counting measure based with two date columns

Hi

Look at my example

balrajahlawat
Esteemed Contributor

Re: creating counting measure based with two date columns

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

Not applicable

Re: creating counting measure based with two date columns

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

Not applicable

Re: creating counting measure based with two date columns

You are just loading data ...

Not applicable

Re: creating counting measure based with two date columns

Thanks

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

MVP
MVP

Re: creating counting measure based with two date columns

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:

YearMonthStatusCount(Distinct [Ticket no])
Jan 2014Created2
Jan 2014Modify2
Jan 2014SUBMIT2
Feb 2014CLOSED1
Feb 2014Created1
Feb 2014Modify1
Mrz 2014Created1
Mrz 2014Modify1
Aug 2014Created2
Aug 2014Modify2
Aug 2014SUBMIT1
Okt 2014Created1
Okt 2014Modify1
Okt 2014SUBMIT2
Nov 2014CLOSED1
Nov 2014Created1
Nov 2014Modify1
Nov 2014SUBMIT1
Dez 2014CLOSED1
Dez 2014Created1
Dez 2014Modify1
Dez 2014SUBMIT1

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.

Not applicable

Re: creating counting measure based with two date columns

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