Issue with Distinct Function

[#1]

Hi All,

We are facing an issue with regards to a chart in our project:

We are trying to figure out the number of Treatment done in a particular Year, Month and Week of the month.

We are getting correct answer when we view the bar chart at year level, however when we drill down to Month or Week of the Month level we get incorrect answer.

Expected answer:

If a patient with code"DM-Jun-10" enrolls for a treatment called "Root Canal" (just for example) and it takes three visits to complete one full treatment, the data should roll up and show one treatment.

Now, when the treatement date spans acros Jun and July for the same treatment, patient code and tooth number, we should get aswer as one treatment.

Please find the attached excel for your reference.

Current Answer: (for understanding purpose)

For the Patient code "DM-Ju-10", treatment "Root Canal" and Tooth Number "27", we have three entries for this combition and the time duration ranges from June to July.

Per our current expression: =count(DISTINCT([TPatientCode])&'-'&([Tooth Number]))

We are getting 1 treatment for June and 4 treatment for July

Ideally we should be getting, 1 treatment for Jun and 3 treatment for July.

Please advise.

[#2]

count(DISTINCT [TPatientCode])&'-'& [Tooth Number]

ortherwise

count(DISTINCT [TPatientCode])&'-'& count(DISTINCT [Tooth Number])

regards -bika

[#3]

Hi Bika_2010,

The above resolution did not work.

[Anonymous]

It seems like you want to count only the first entry of a treatment which is the first time visit for the particular treatment. If thats the case, you can create an indicator field to indicate whether the entry is the first time visit for a treatment from the script, then count(if(FirstTimeVisitIndicator='1',TPatientCode))

You can also refer to the attached sample.

[#5]

Hi Jitiong,

You got the idea right.

Imagine if we have 100,000 rows in an excel or in our data base, we cannot keep on adding the "FirstTimeVisitIndicator=1".

Can we write a expression which can make this logic run.

I am using personel version of QV, hence cannot open any other application.

[Anonymous · Accepted solution]

In fact, the performance is better by adding the "FirstTimeVisitIndicator=1" than writing a complex expression in the front end because when you have a complex expression in the front end it takes processing power to perform the calculation, and it is every click counts.

If the data that you have is not too huge, you can try the expression below:

Count(if([Treatment Date]=aggr(min(TOTAL <TPatientCode> [Treatment Date]),TPatientCode),TPatientCode))

Hopefully it works

[#7]

Thanks Jitiong, your query worked.