Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Chart to show accumulated backlog

I all, I have a table with the headers below for casework:

Case IDOpened DateClosed DateClosed
101 Jan 201605 Jan 2016Yes
201 Jan 201603 Jan 2016Yes
302 Jan 201611 Jan 2016Yes
404 Jan 2016No
507 Jan 2016No

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.

backlog example.png

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.

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

3 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable
Author

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?

r_wroblewski
Partner - Creator III
Partner - Creator III

Dear Simon,

I just looked at the qvw and Kaushik used following:

     Dimension: Date

     Expression: Count(Distinct [Case ID])

Regards,

Ronny