1 2 Previous Next 19 Replies Latest reply on Jun 15, 2018 6:51 AM by Seth Nelson

# Calculation of Unique Count

Hello All,

In my data source I have two sheets and I have joined two sheets with common field "Cocd_DocNo".

I have two systems SAP & Dolphin. Only for SAP lines I have values for "Cocd_DocNo" header and would it will null for Dolphin.

I need a get a count of lines which are sheet 1. The total count is 30 in sheet 1 but I am unable to get that count. I was able to combine the amount but not the count.

My desired result from the data source is total amount -196214.07 and count = 30

Need help

Regards,

Chandrakanth.K

• ###### 1. Re: Calculation of Unique Count

Hi,

Try creating a calculated field for Count as below:

Count: IF [Cocd_DocNo] = "SAP" THEN 1 ELSE 0 END.

Regards

Sohan.

• ###### 2. Re: Calculation of Unique Count

Hello Sohan,

I am unable to use IF [Cocd_DocNo] = "SAP" THEN 1 ELSE 0 END. since it cant compare integer and string values.

Regards,

Chandrakanth.K

• ###### 3. Re: Calculation of Unique Count

Hello Chandrakanth,

Please see Calculation1 in the attached .twbx. Achieved using similar method as "Combined amount" calculation.

Hope this helps,

Seth

• ###### 4. Re: Calculation of Unique Count

Sorry I meant [System]="SAP".

• ###### 5. Re: Calculation of Unique Count

Hello Seth,

I am unable to open the workbook.

Regards,

Chandrakanth.K

• ###### 6. Re: Calculation of Unique Count

Hello Sohan,

I used below calculated field but its not providing me accurate count.

IF [System] = "SAP" THEN 1 ELSE 0 END

Its giving me count in second sheet. I need count in first sheet which 30.

Regards,

Chandrakanth.K

• ###### 7. Re: Calculation of Unique Count

Hello Seth,

I am using Tableau 10.5.3 Version so unable to open your workbook. Can you share the calculated field please?

Regards,

Chandrakanth.K

• ###### 8. Re: Calculation of Unique Count

Hello Chandrakanth,

Version 10.5 attached.

Calculation1:           IFNULL(COUNT([Cocd DocNo]),NULL)

Thanks,

Seth

• ###### 9. Re: Calculation of Unique Count

Hello Seth,

I used below calculated field but its not providing me accurate count.

IFNULL(COUNT([Cocd DocNo]),NULL)

Its giving me count of second sheet. I need count of first sheet which is 30.

Regards,

Chandrakanth.K

• ###### 10. Re: Calculation of Unique Count

Hello Chandrakanth,

I'm not sure I am understanding where the count of 30 should be coming from. The attached image shows the total returns colored by system.

This shows 15 returns for Dolphin and 27 for SAP. Maybe an issue with the join clause?

Thanks,

Seth

• ###### 11. Re: Calculation of Unique Count

Hello Seth,

I have two sheets. Sheet 1 have count of 30 and sheet 2 have count of 27

27 lines which I have in sheet 2 is  split of amount profit center wise for SAP lines.

So I need count only sheet 1 which have 30 lines ( SAP 15 and Dolphin -15)

I need unique value for SAP ( 15)

Regards,

Chandrakanth.K

• ###### 12. Re: Calculation of Unique Count

Hello Seth,

I tried the below calculated field. but in this calculation I need distinct count  only for Cocd_DocNo.

Is there a way to do this?

IFNULL([Cocd_DocNo],[Cocd_VendorNo])

Regards,

Chandrakanth.K

• ###### 13. Re: Calculation of Unique Count

Hi Chandrakanth,

I don't think I'm clear on your requirement. Attached is a workbook v10.5 that shows 15 returns for each system type.

Let me know if this helps.

Thanks,

Seth

• ###### 14. Re: Calculation of Unique Count

Hello Seth,

How were you able to get the count 15 for each system type? I dragged the company code to rows but in my work book I am seeing 27 count for SAP.

My requirement.

In sheet one I have 30 records ( SAP 15 & Dolphin 15). In Sheet 2 I have 27 records ( SAP only).

27 records in sheet two is a split of 15 SAP records in Sheet 1. Unique ID to connect both the sheet is Cocd_DocNo.

I am able to get the correct count of SAP records when I use Cocd_DocNo field to count but I am unable to count for Dolphin since I don't have values in Cocd_DocNo field for dolphin.

I though of an alternative I,.e.. create a Row ID in sheet 1 and use that to count number of records. but these data is extracted by BOT and I am unable to make any changes to data source.

Is there a way I can create a Row ID in tableau or any other way where I can count just sheet 1?

Regards,

Chandrakanth.K

1 2 Previous Next