How to use Distinct function.

[#1]

Dear All,

We are creating a bar graph for our project.

The requirement is to dispaly the number of Treatment done.

Per our data in the excel sheet, if we use = (count[Paitent Code]), as our expression we get the total number of treatment done. However, there are mutliple suitation where the treatment is not completed in a single visit, we need to consolidate the treatment for a specific patient and treatment into a single entry.

For example:

We have one paitent for a treatment called Root Canal and the patient it took three visit to complete the treatment on one single tooth, if we use = (count[Paitent Code]), the answer we get is 3, the expected answer is 1.

Please find the attached sample excel file. Need our help.

[kaushiknsolanki]

Hi,

     Try this.

     Count(Disitnct [Paitent Code]&'-'&Treatment])

Regards,

Kaushik Solanki

[#3]

Hi,

We got the correct answer using your query.

We are using the following query:

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

However we are facing a new challenge. Please refer row number 10 and 13 in the above attached excel sheet.

Only one treatment of Root canal was done for patient 'DM-Jun-10 ' starting 06/22/2013 and second visit was on 07/05/2013. We are getting correct answer when we see year level data (ie.4), but when we drill down to month level we are getting one treatment of root canal for June and the four entry for July i.e this entry is getting repeated in July which is incorrect.

[kaushiknsolanki]

Hi,

     Yes cause it will only count for the month of July.

     What output you are expecting.

Regards,

Kaushik Solanki    

[#5]

The Expected output for this specific case of Root Canal is as follows:

We have created a group called Drill Down (Year, Month)

Year :4

Month: 4 (June: 1, July:3)

Currenlty we are getting

Year: 4

Month: 5 (June: 1, July:4)