1 Reply Latest reply: Oct 1, 2018 1:40 PM by Nicole Smith RSS

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

    Jonathan Wallace

      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.

        • Re: Create date range as x-axis for bar chart (frequency graph)
          Nicole Smith

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

          patient_stay_iddatedate_type
          12018-01-01Admission
          12018-01-03Discharge
          22018-01-01Admission
          22018-01-01Discharge

           

          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)