Formula in TextBox

[#1]

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

[ramoncova06]

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

[#3]

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

[ramoncova06 · Accepted solution]

Hi Daniel,

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

[#5]

Dear Ramon

It works! Thank You again for Your help¨

Regards,

Daniel