Calculating unique counts with different criteria

Difficulty

Objective

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.

Solution

<base table="pub.doc.retail.altseg.sales_detail_transid"/>
<willbe name="store_week" value="if(between(trans_date;20160403;20160409);
store;NA)"/>
<willbe name="store_month" value="if(between(trans_date;20160403;20160430);
store;NA)"/>
<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"/>
</tabu>

Discussion

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:

<tabu>

if(C1;R1;C2;R2;...;D)