-
1. Re: Conditional Count and Percentage problem
pooja.gandhi May 18, 2016 1:03 PM (in response to Xianan Zhao)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.
-
sample book.twbx 195.2 KB
-
-
2. Re: Conditional Count and Percentage problem
Xianan Zhao May 18, 2016 1:13 PM (in response to pooja.gandhi)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
pooja.gandhi May 18, 2016 1:22 PM (in response to Xianan Zhao)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
Xianan Zhao May 18, 2016 1:35 PM (in response to pooja.gandhi)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
pooja.gandhi May 18, 2016 1:48 PM (in response to Xianan Zhao)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!
-
sample book (1).twbx 212.6 KB
-
-
6. Re: Conditional Count and Percentage problem
Xianan Zhao May 18, 2016 2:01 PM (in response to pooja.gandhi)Amazing! Thank you so much Pooja.