10.5 Calculate average daily sale amount in a quarter
Calculate the daily average sale amount in each quarter.
Calculate average daily sale amount in each quarter of the year 2014. Below is part of the sale data:
| ORDERID | CUSTOMERID | EMPLOYEEID | ORDERDATE | AMOUNT |
|---|---|---|---|---|
| 10400 | EASTC | 1 | 2014/01/01 | 3063.0 |
| 10401 | HANAR | 1 | 2014/01/01 | 3868.6 |
| 10402 | ERNSH | 8 | 2014/01/02 | 2713.5 |
| 10403 | ERNSH | 4 | 2014/01/03 | 1005.9 |
| 10404 | MAGAA | 2 | 2014/01/03 | 1675.0 |
| … | … | … | … | … |
days(dateExp) function is used to get the number of days in a year/quarter/month the specified date dateExp belongs to. It can work with @q option to get the number of days in a quarter a date specified belongs to.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from Sales”) |
| 3 | =A2.select(year(OrderDate)==2014) |
| 4 | =A3.groups((month(OrderDate)+2)\3:Quarter; sum(Amount):Amount) |
| 5 | =A4.run(Amount=Amount / days@q(date(“2014/”/(Quarter*3)+“/01”))) |
A1 Connect to data source.
A2 Import Sales table.
A3 Get records of the year 2014.
A4 Group the selected records by quarter and calculate the total sale amount in each quarter.
A5 Use days() function to calculate the number of days in each quarter, and divide the total sale amount by this number to get the average daily sale amount.
Execution result:
| Quarter | Amount |
|---|---|
| 1 | 1765.33 |
| 2 | 1764.96 |
| 3 | 2034.56 |
| 4 | 2355.63 |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
