Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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 |
Hi Anil,
It is not able to recognize the DateField. Can you pls assist.
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.
Hi,
in Dimension : Month(OpenMonth)
Regards,
Antonio
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
Why not ?
It also works.