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

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

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

• Re: Formula in TextBox

Hi Daniel,

You are right, for some reason I thought your requirement was to only show the last 25 days , your syntax should work

• Re: Formula in TextBox

Dear Ramon

It works! Thank You again for Your help¨

Regards,

Daniel