Data Count between Dates

[#1]

Hi All,

I have the below data

Ticket No	Open Date (MM/DD/YYYY)	Close Date(MM/DD/YYYY)
1	1/10/2017	3/10/2017
2	2/15/2017	5/20/2017
3	3/11/2017	6/22/2017

And I want the final Output of total number if tickets per month as

Jan Open Ticket	Feb Open Ticket	Mar Open Ticket
1	2(1 of Feb and 1 backlog of Jan)	2(1 For Mar and 1 for backlog Feb, Jan ticket got closed in March hence not considered)

I am looking for a generic solution to populate the count if the ticket falls under a date range.

Thank you in advance.

Message was edited by: Manpreet Singh

[Anil_Babu_Samineni]

One solution may be this?

                                                                      Script:

Sample1:

LOAD [Ticket No], [Open Date (MM/DD/YYYY)] as DateField, 'Open Date' as Flag

FROM [https://community.qlik.com/message/1310037]

(html, codepage is 1252, embedded labels, table is @1);

Concatenate

LOAD [Ticket No], [Close Date(MM/DD/YYYY)] as DateField, 'Close Date' as Flag

FROM [https://community.qlik.com/message/1310037]

(html, codepage is 1252, embedded labels, table is @1);

Final:

NoConcatenate

LOAD [Ticket No], DateField, Month(DateField) as MonthField, Flag Resident Sample1;

DROP Table Sample1;

                                                                      Front End

1) Create Straight table

          Dimension is MonthField

2) and expression is

Count({<Flag = {'Open Date'}>}[Ticket No])

3) Give Accumulate 2 steps back

HTH

Anil

[antoniotiman]

Hi,

try this

LOAD *,MonthName(OpenDate,IterNo()-1) as OpenMonth Inline [
TicketNo, OpenDate, CloseDate
1, 1/10/2017, 3/10/2017
2, 2/15/2017, 5/20/2017
3, 3/11/2017, 6/22/2017
] While MonthName(OpenDate,IterNo()-1) < MonthName(CloseDate);

Chart :

Dimension ; OpenMonth

Expression Count(DISTINCT TicketNo)

Regards,

Antonio

[#4]

Hello Antonio,

Is it possible to show the data in below format? Remove the year '2017' from the header.

Jan	Feb	March
1	2	2

[#5]

Hi Anil,

It is not able to recognize the DateField. Can you pls assist.

[#6]

Hi Anil ,

The solution is not adding previous month open tickets, for e.g. for month of Feb , Jan open tickets are not getting added. Pls ahelp.

[antoniotiman]

Hi,

in Dimension : Month(OpenMonth)

Regards,

Antonio

[#8]

Thanks Much.

One Challenge which I am facing is if the open date is year 2015 or 2016 and close date in 2017 in that scenario the record is not considered.

Can you pls assist

[antoniotiman]

Why not ?

It also works.