Basic Reports: Total quantity and amount purchased by age group

In this last lesson in Basic Commands, we will walk you through the process of creating a report classifying total quantity and amount by age group.

The Output

Total quantity and amount purchased by age group

Basic Commands Tutorial

Age Quantity Amount
10 879 335936
20 4542 1756687
30 19030 7416259
40 17381 6766661
50 9672 3757351
60 1689 655321
70 250 98641

The process flow

Customer's birthday information is usually collected when they register as a member. There are two methods to calculate the customer's age. The first is based on the purchase date of each customer, on the other hand, the age varies throughout the years. The second method calcuated the customer's age based on a fixed current date, however, the age recorded will not be accurate corresonding to their actual age overtime. We will therefore need to be careful when deciding on the method of calculation.

By classifying sales according to age group, we can identify to most profitable age group. It is common to analysis multiple variables such as total amount, quantity, and gross profit to gain a more well rounded perspective.

In this lesson, we will use the basic commands we have learned previously to generate the report. Below is the process flow, think through each step carefully before proceeding

Answer: The process flow

  1. Remove records with null customer and date of birth
  2. Select required fields: "date of birth", quantity, and amount
  3. Set 2003/01/01 as the current date and calculate the age
  4. Filter out records outside the range of age 10 to 90
  5. Sum up Total quantity and amount by age
  6. Extract columns "age", "quantity", and "amount"

Script

After we have prepared the work flow, let's rethink each step and start creating the script.

#/bin/bash
#===============================================================
# MUSASHI bash script
#===============================================================

#---- Title
title="Total amount and quantity by age"

#---- Comment
comment="Basic Lessons"

#---- Variables
inPath="/home/public/tutorial"

#---------------------------------------------------------------
# Commands
#---------------------------------------------------------------
xtdelnul -f customer -i $inPath/dat.xt |
xtcut -f customer,quantity,amount |
xtjoin -k customer -m $inPath/cust.xt -f dateofbirth |
xtcal -c 'down(age("20021231",$dateofbirth,10)' -a age |
xtsel -c '$age>=10 && $age<=90' |
xtcut -f age,quantity,amount |
xtagg -k age -f quantity,amount -c sum |
xtcut -f age,quantity,amount |
xtheader -l "$title" -c "$comment" -o age.xt
#===============================================================

Check your results after running the script

Exercise

The following are additional exercises for this lesson, try them out and check your results with the scripts and output given below.

Report Name Script Name Result (xt) Result (html)
The age group with the most amount and quantity purchased age1.sh age1.xt age1.html
The age group with the most amount and quantity purchased on the first 5 months age2.sh age2.xt age2.html
The age group with the most visits to the store on the last 4 months age3.sh age3.xt age3.html
Gross profit margin by age group (at intervals of 4) age4.sh age4.xt age4.html