Need help on creating a Bar chart

[Anonymous]

Hello,

I have my raw data which tells me a case opened date, closed/resolved date, and the category each case belongs to. I want to calculate the number of cases remain open in each category each day and show the results with a bar chart. Is there any way we can plot this bar chart in Qlikview given the raw data? Can anyone help me with it?

Here is my Raw Date:

issue_id	opened_date	resolved_date	category
1	6/22/2016	6/26/2016	Equipment
2	6/24/2016	6/25/2016	Human
3	6/24/2016	6/26/2016	Equipment
4	6/25/2016	6/26/2016	Other
5	6/25/2016	6/26/2016	Equipment
6	6/26/2016		Human
7	6/26/2016		Equipment
8	6/26/2016		Other

Then we calculate the number of cases remain open in each category each day (i.e. new open cases + old non-resolved cases):

Date	Equipment	Human	Other
6/22/2016	1	0	0
6/23/2016	1	0	0
6/24/2016	2	1	0
6/25/2016	3	0	1
6/26/2016	1	1	1

and I want to show the result in the above table in a bar chart like this:

Thank you,

Yolanda

[sunny_talwar]

I have an almost same result as yours, except for the last two dates. Can you explain the logic behind what you have

[Anonymous]

Hi Sunny,

As for the last two dates: On date 06/25/2016, one case (issue_id = 2, in Human category) was closed/resolved, so we have 0 case under Human category remains open that day.

On date 06/26/2016, two cases under "Equipment" category were closed/resolved, and two cases under "Other" category were closed, therefore in the end of that day, there were 3 cases in total remain open ((issue_id = 6, 7, 8).

Not sure if I make this clear? Please let me know if there's anything you would like me to explain further. Thank you very much for your help!!

Thanks,

Yolanda

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD *,

  Date(opened_date + IterNo() - 1) as date

While opened_date + IterNo() - 1 < resolved_date1;

LOAD issue_id,

    opened_date,

    resolved_date,

    If(Len(Trim(resolved_date)) = 0, opened_date + 1, resolved_date) as resolved_date1,

    category

FROM

[https://community.qlik.com/thread/222700]

(html, codepage is 1252, embedded labels, table is @1);

[Anonymous]

This works perfectly! Thank you very much, Sunny!!

Thanks,

Yolanda