Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I all, I have a table with the headers below for casework:
Case ID | Opened Date | Closed Date | Closed |
---|---|---|---|
1 | 01 Jan 2016 | 05 Jan 2016 | Yes |
2 | 01 Jan 2016 | 03 Jan 2016 | Yes |
3 | 02 Jan 2016 | 11 Jan 2016 | Yes |
4 | 04 Jan 2016 | No | |
5 | 07 Jan 2016 | No |
I'd like to show the below chart to show what the backlog was at a given time and simply see how many cases were left open at the end of a day. I made an example chart below in excel to illustrate my need. I used the simple data above in this chart.
I have been playing around with rangecount but I can't make it work. Any help appreciated.
PS: I looked around but I can't find a solution anywhere for QlikSense.
Hi,
Have a look at the attachment.
Below is the script.
Data:
LOAD [Case ID],
Date(Date#([Opened Date],'DD MMM YYYY')) as [Opened Date],
if(Isnull([Closed Date]),MakeDate(9999),Date(Date#([Closed Date],'DD MMM YYYY'))) as [Closed Date],
Closed
FROM
[https://community.qlik.com/thread/239141]
(html, codepage is 1252, embedded labels, table is @1);
Let vMin = num(MakeDate(2016));
Let vMax = Num(Makedate(2016,01,31));
Cal:
Load Date($(vMin) + RowNo() -1) as Date
AutoGenerate 1
While Date($(vMin) + RowNo() -1) <Date($(vMax));
IntervalMatch(Date)
Load [Opened Date],[Closed Date] Resident Data;
Regards,
Kaushik Solanki
Hi,
Have a look at the attachment.
Below is the script.
Data:
LOAD [Case ID],
Date(Date#([Opened Date],'DD MMM YYYY')) as [Opened Date],
if(Isnull([Closed Date]),MakeDate(9999),Date(Date#([Closed Date],'DD MMM YYYY'))) as [Closed Date],
Closed
FROM
[https://community.qlik.com/thread/239141]
(html, codepage is 1252, embedded labels, table is @1);
Let vMin = num(MakeDate(2016));
Let vMax = Num(Makedate(2016,01,31));
Cal:
Load Date($(vMin) + RowNo() -1) as Date
AutoGenerate 1
While Date($(vMin) + RowNo() -1) <Date($(vMax));
IntervalMatch(Date)
Load [Opened Date],[Closed Date] Resident Data;
Regards,
Kaushik Solanki
I can't open the qvw file as I only have the personal edition of QlikView. My question was designed for QlikSense but however, I have recreated the script in my QlikSense but do not know which measure to use to build the chart. Could you outline it here please?
Dear Simon,
I just looked at the qvw and Kaushik used following:
Dimension: Date
Expression: Count(Distinct [Case ID])
Regards,
Ronny