Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r092
Excerpt

This example demonstrates how to count the number of values within a group, based on a specified conditional test.

Functions:

D generate list excerpts
pagesCOUNTIF Function,COUNTAIF Function

Source:

The following data identifies sales figures by salespeople for a week:

EmployeeIdDateSales
S0011/23/1725
S0021/23/1740
S0031/23/1748
S0011/24/1781
S0021/24/1711
S0031/24/1725
S0011/25/179
S0021/25/1740
S0031/25/17 
S0011/26/1777
S0021/26/1783
S0031/26/17 
S0011/27/1717
S0021/27/1771
S0031/27/1729
S0011/28/17 
S0021/28/17 
S0031/28/1714
S0011/29/172
S0021/29/177
S0031/29/1799

 

Transformation:

You are interested in the count of dates during the week when each salesperson sold less than 50 units, not factoring the weekend. First, you try the following:

D trans
RawWrangletrue
p03Value1
Typestep
WrangleTextpivot value:COUNTIF(Sales < 50) group:EmployeeId limit:1
p01NameRow labels
p01ValueEmployeeId
p02NameValues
p02ValueCOUNTIF(Sales < 50)
p03NameMax columns to create
SearchTermPivot columns

You notice, however, that the blank values, when employees were sick or had vacation, are being counted. Additionally, this step does not filter out the weekend. You must identify the weekend days using the WEEKDAY function:

D trans
RawWrangletrue
p03Value'DayOfWeek'
Typestep
WrangleTextderive type:single value:WEEKDAY(Date) as:'DayOfWeek'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueWEEKDAY(Date)
p03NameNew column name
SearchTermNew formula

If DayOfWeek > 5, then it is a weekend date. For further precision, you can use the COUNTAIF function to remove the nulls:

D trans
RawWrangletrue
p03Value1
Typestep
WrangleTextpivot value:COUNTAIF(Sales, DayOfWeek<6) group:EmployeeId limit:1
p01NameRow labels
p01ValueEmployeeId
p02NameValues
p02ValueCOUNTAIF(Sales, DayOfWeek<6)
p03NameMax columns to create
SearchTermPivot columns

The above counts the non-null values in Sales when the day of the week is not a weekend day, as grouped by individual employee.


Results:

EmployeeIdcountaif_Sales
S0015
S0024
S0034

 

D s also
labelexample_countif_functions