Create date range as x-axis for bar chart (frequency graph)

[Anonymous]

Hello

I need to graph the number of admission and discharges to a medical unit each day. I would like to create a bar chart with the date range as the x-axis and on the y-axis have the number of admissions and discharges each day.

I have a table (fact_ward_stay) that includes

- patient_stay_id

- stay_start_date [date of admission]

- stay_end_date [date of discharge]

I'm not sure how to make the date range on the x-axis that I would plot against the counts of stay_start_date and stay_end_date.

I know how to create a separate table of dates in the script but not sure if this would be helpful, or whether the solution is created in the script or in the chart.

Any help would be much appreciated! I'm new to Qlik and answering this query would be very helpful to my work.

Thanks in advance.

[Nicole-Smith]

I would restructure your data in the script to look like this:

patient_stay_id	date	date_type
1	2018-01-01	Admission
1	2018-01-03	Discharge
2	2018-01-01	Admission
2	2018-01-01	Discharge

You could transform the table in the script using code like this:

final:
LOAD patient_stay_id,
     stay_start_date AS date,
     'Admission' AS date_type
RESIDENT fact_ward_stay;

CONCATENATE(final)
LOAD patient_stay_id,
     stay_end_date AS date,
     'Discharge' AS date_type
RESIDENT fact_ward_stay
WHERE IsNull(stay_end_date) = 0;

DROP TABLE fact_ward_stay;

Then to make your chart:

Dimension:

date

Expressions:

1.  Admissions:

count({<date_type = {'Admission'}>}distinct patient_stay_id)

2.  Discharges:

count({<date_type = {'Discharge'}>}distinct patient_stay_id)