Calculating unique counts with different criteria

Calculating the unique count of a specific column can be accomplished with a tabulation. However, calculating that unique count for two different situations requires more manipulation.



You want to calculate the number of stores each customer visited during a specified week and during a specified month. Performing a unique count tabulation, using Store as the source column and grouping on Customer, will result in the number of stores each customer visited during the given time period. However, you need this number broken up into the number of stores per week and per month.


<base table="pub.doc.retail.altseg.sales_detail_transid"/>
<willbe name="store_week" value="if(between(trans_date;20160403;20160409);
<willbe name="store_month" value="if(between(trans_date;20160403;20160430);
<sel value="store_month<>NA"/>
<tabu label="Tabulation" breaks="customer">
  <break col="customer" sort="up"/>
  <tcol fun="ucnt" name="ucnt_store_week" source="store_week" 
  label="Unique Count`Store for Week"/>
  <tcol fun="ucnt" name="ucnt_store_month" source="store_month" 
  label="Unique Count`Store for Month"/>


Before performing the tabulation, two new source columns are created, one for the specified week, and one for the specified month. If the store visit took place during the specified time period, the store number is recorded in the column, if not, an NA value is given. Then a selection is performed to only include the rows that occur in the given time period.

You can then perform a tabulation using store_week and store_month as your source columns and ucnt as your function.

Further reading

If you would like to learn more about the functions and operations discussed in this recipe, click on the links below: