topic Re: Data Count between Dates in QlikView

Data Count between Dates

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Data Count between Dates

Anil_Babu_Samineni — Thu, 20 Jul 2017 03:44:09 GMT

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

Re: Data Count between Dates

antoniotiman — Thu, 20 Jul 2017 05:19:25 GMT

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

Re: Data Count between Dates

Sun, 23 Jul 2017 07:21:14 GMT

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

Re: Data Count between Dates

Sun, 23 Jul 2017 11:16:41 GMT

Hi Anil,

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

Re: Data Count between Dates

Sun, 23 Jul 2017 11:44:15 GMT

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.

Re: Data Count between Dates

antoniotiman — Mon, 24 Jul 2017 09:15:39 GMT

Hi,

in Dimension : Month(OpenMonth)

Regards,

Antonio

Re: Data Count between Dates

Mon, 24 Jul 2017 16:40:36 GMT

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

Re: Data Count between Dates

antoniotiman — Mon, 24 Jul 2017 18:35:25 GMT

Why not ?

It also works.