4 Replies Latest reply: Jun 30, 2015 6:53 AM by Daniel Strub RSS

    Formula in TextBox

      Dear QlikView Community

       

      I'am new in this forum and have little experience with QlikView (using a german version).I will be thankful for every help.

       

      Current problem:

       

      I have a table 'tbl_LEP' with the four columns "Datum", "Station", "PID" and 'LEP_Variable'.

      (Context: Hospital, Date of treatment, Station = denomination of the Ward where the treatment took place, PID = Patient ID, LEP_Variable = identifier of the treatment for the accounting)

       

      DatumStationPIDLEP_Variable
      01.06.20151.2562131.13.2.6
      01.06.20151.2562131.16.3
      01.06.20151.2562131.8.22
      01.06.20153.3568881.13.2
      01.06.20153.3568881.2.2
      01.06.20153.45212316.2
      02.06.20151.2562131.13.5
      02.06.20151.2562131.11.26
      02.06.20153.3568881.6.5
      02.06.20153.3568881.15.6
      02.06.20152.2795641.5.6
      02.06.20152.2485421.11.26
      03.06.2015NOTF454511.18.2
      03.06.2015NOTF454511.12.2

       

      At a specific date a patient can get several treatments (LEP_Variable).

       

      For a given time selection (e.g. June 2015) I like to count how many Patient (PID) have been seen.

       

      Therefore I have to identify how many different patient have been in the hospitalon  each day  (DISTINCT PID) ans sum theses values over the selected time (e.g. the 30 days of june 2015).

       

      For the table above de solution would be:

      3 different Patient (the PIDs 56213, 56888 and 52123) on 01.06.2015

      4 different Patient (the PIDs 56213, 56888, 79564 and 48542) on 02.06.2015

      1 different Patient (the PID 45451) on 03.06.2015

       

      Total Patient-Days in June 2015: 8

      I like to calculate and show this value (8) in a TextBox but I can't find the right formula. Who can help me?

       

      Regards,

      Daniel

        • Re: Formula in TextBox
          Ramon Covarrubias

          The issue is that you are doing a count distinct, and unless you use aggr QV will normally show the total as a whole, use the following expression

           

          =SUM(AGGR(count({<Datum={">=$(=date((today()-25),'dd.MM.yyyy'))"}>}DISTINCT PID),Datum))

           

          check the following post to get more info on how QV expressions work

          A Primer on Set Analysis

          QlikView Technical Brief - AGGR.docx

          https://community.qlik.com/docs/DOC-4951

            • Re: Formula in TextBox

              Dear Ramon

               

              Thank You for Your help. I have studied the linked documents und especially the first two (A Primer on Set Analysis and QlikView Technical Brief - AGGR) have been very useful and easy to read.

               

              After the lecture of theses documents I was able to understand the construction of the your suggested expression

              =SUM(AGGR(count({<Datum={">=$(=date((today()-25),'dd.MM.yyyy'))"}>}DISTINCT PID),Datum))

               

              Why do we need  $(=date((today()-25),'dd.MM.yyyy')  as modifier in the set analysis?

              My QV worksheet has List boxes for Year and Month and the current selection is  "2015" and "June".

              Why is =SUM(AGGR(count(DISTINCT PID),Datum)) not enough?

               

              QV will create a virtual Table with the columns "Datum" und "count(DISTINCT PID)" and sum the values of the column "count(DISTINCT PID)" over all rows (my understanding of page 8 in the document "QlikView Technical Brief - AGGR"). In my example it sould result the value 8.

               

              Where is my error in reasoning?

               

              Regards,

              Daniel