6 Replies Latest reply on May 18, 2016 2:01 PM by Xianan Zhao

# Conditional Count and Percentage problem

Hi all,

I need some help on the conditional count in Tableau. It always shows the "cannot mix aggregate and non-aggregate arguments" error.

I would like to get:

1) a total number of orders by DivisionID, total count of orders that are "double keyed", the percentage of the "double keyed" orders

2) total count of orders which have a "process to approve period" less than 10 minutes, and the percentage in total

Message was edited by: Xianan Zhao

• ###### 1. Re: Conditional Count and Percentage problem

Hi There!

Look at the attached workbook. If you group by division id and just create calculated fields as shown in the workbook, you should have the 1st question answered. You can write similar calculations for the 2nd one. Let me know if you have questions!

Pooja.

• ###### 2. Re: Conditional Count and Percentage problem

Thank you Pooja.

I got the first problem solved. However the 2nd one is a little bit different. I blame the sample book. In my workbook, the "Process to Approve" is a calculated field in measure. It sets the order numbers which have a process period less than 10 minutes to "Less than 10 minutes", else are "Above 10 minutes". I wish I could use a calculated field to do the count, but it will mix the aggregate and non-aggregate arguments, for example : COUNTD ( IF [Process to Approve] = 'Less than 10 minutes' THEN [Order Number] END)

• ###### 3. Re: Conditional Count and Percentage problem

I would suggest giving a mock up example that mimics your actual dataset. The solution is a lot different and depends largely on the dataset. I don't see a 'process period' field in the dataset. Is that a time field?

• ###### 4. Re: Conditional Count and Percentage problem

This updated sample is more like the original one with approved date and processed by date time. What I want is to count the number of orders which [approved date] - [process by date time] are less than 10 minute. I used:

ZN( DATEDIFF('minute', ATTR([Approved Date]), ATTR([Processed By Date Time]))) to get the minutes. Right now I just need to count those.

• ###### 5. Re: Conditional Count and Percentage problem

Your dates were being recognized as strings in the dataset you attached. So I changed it to an 'actual' date format and used those fields instead of your original.

Your datediff formula just needs to be:

DATEDIFF('minute', [Approved Date Calc], [Processes Date Calc])

And to count orders that have less than 10 mins datediff:

{ fixed [Division ID], [Order Number] : countd(if [Datediff] <= 10 then [Order Number] end )}

See attached!

• ###### 6. Re: Conditional Count and Percentage problem

Amazing!  Thank you so much Pooja.