Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
simontouyet
Contributor
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
kaushiknsolanki

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

3 Replies
kaushiknsolanki

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
simontouyet
Contributor
Contributor
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
Partner

Dear Simon,

I just looked at the qvw and Kaushik used following:

     Dimension: Date

     Expression: Count(Distinct [Case ID])

Regards,

Ronny