topic SUM Distinct in QlikView

SUM Distinct

Mon, 12 Jul 2010 22:59:33 GMT

I have a fact table containing patient data. For example, patient is treated for Disease A , Disease B or combination AB. A patient might have multiple entries in a fact table among different Disease group. Now, when I sum on patient for an individual group I get the total from all three groups. How can I tell QV to do a distinct on a patient. If patient has already included in a count for one group DONT count it another time.

Please let me know if you need additional info.

SUM Distinct

johnw — Mon, 12 Jul 2010 23:46:28 GMT

I'm not sure I understand the question.

If you don't have disease as a dimension in your chart, count(distinct patient) will only count the patients once. If you DO have disease as a dimension in your chart, WHICH disease did you want to include the patient under if they're been treated for multiple diseases? QlikView isn't making the decision for you, which is why you'll see the patient counted under each disease. However, if you do a total for the diseases, it will then count correctly again (unless you select sum of rows).

SUM Distinct

Mon, 12 Jul 2010 23:51:49 GMT

You can use set expressions to achieve this:

count({<disease={'A'}>} patient)

SUM Distinct

Tue, 13 Jul 2010 00:56:27 GMT

John You understood the question right. I have disease as a dimesion in my table chart. I have given a group_code for a patient and disease where patient belongs to. If patient has code A for a particular date, and he occurs under code B on the same date I want to exclude one. I know its hard for you to understand. I will share the app soon.

SUM Distinct

Wed, 14 Jul 2010 00:57:59 GMT

How would you write the following SQL statement as a QV expression. My fact table is MONTH_PTNT_CNT which has data at regimen level(a lowest regimen level = RGMN_NBR). Another table is REGIMEN_GRP table which states what regimen belongs to which group. It is highly likely that a regimen can belong to more than one regimen_group Or you can say that some regimen_nbr will share parents. In SQL you can easily identify by using IN keyword as stated in the query.

select

sum(TOT_PTNT_CNT)

from MONTH_PTNT_CNT

where s_gid=21

and rgmn_nbr IN (select rgmn_nbr from REGIMEN_GRP where s_gid=21 and rgmn_grp_gid = 15)

and MTH_INT=200907

and dg_combo_nbr=1