topic Formula in TextBox in QlikView

Formula in TextBox

Tue, 16 Jun 2015 12:42:04 GMT

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)

Datum	Station	PID	LEP_Variable
01.06.2015	1.2	56213	1.13.2.6
01.06.2015	1.2	56213	1.16.3
01.06.2015	1.2	56213	1.8.22
01.06.2015	3.3	56888	1.13.2
01.06.2015	3.3	56888	1.2.2
01.06.2015	3.4	52123	16.2
02.06.2015	1.2	56213	1.13.5
02.06.2015	1.2	56213	1.11.26
02.06.2015	3.3	56888	1.6.5
02.06.2015	3.3	56888	1.15.6
02.06.2015	2.2	79564	1.5.6
02.06.2015	2.2	48542	1.11.26
03.06.2015	NOTF	45451	1.18.2
03.06.2015	NOTF	45451	1.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

ramoncova06 — Tue, 16 Jun 2015 17:41:38 GMT

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

Thu, 18 Jun 2015 09:59:27 GMT

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

ramoncova06 — Thu, 18 Jun 2015 14:20:10 GMT

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

Tue, 30 Jun 2015 10:53:09 GMT

Dear Ramon

It works! Thank You again for Your help¨

Regards,

Daniel