Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
simontouyet
New Contributor

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

Re: Chart to show accumulated backlog

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

View solution in original post

3 Replies

Re: Chart to show accumulated backlog

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

View solution in original post

simontouyet
New Contributor

Re: Chart to show accumulated backlog

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?

Partner
Partner

Re: Chart to show accumulated backlog

Dear Simon,

I just looked at the qvw and Kaushik used following:

     Dimension: Date

     Expression: Count(Distinct [Case ID])

Regards,

Ronny