ACL Training Materials Unit C Analysing data characteristics

ACL Training Materials Unit C: Analysing data characteristics 主要參考資料來源: KPMG ACL課程講義資料 Price. Water. House. Cooper ACL課程講義資料

Situations when we would use data analysis • ACL commands that allow us to perform data analysis: • Data validity commands; • Analysis of numeric fields and values; • Analysis of non-numeric fields and values.

Analysing data characteristics Numeric fields

Analysing data characteristics (數值) • Count • Total • Statistics • Profile • Stratify

Count • Count - counts the number of records that meet the specified condition;

Total • See if you can derive the total value of the invoices (ie voucher type ‘IN’) that were billed before 1997 • 可驗證資料之完整性

Total

Total Pricewaterhouse. Coopers

Statistics and Profile (統計分析) • Statistics - returns a statistical summary of one or more numeric fields; • Profile - returns similar but slightly less detailed information; • Run the statistics command on the value field.

Statistics and Profile (統計分析) Total value of receivables $468, 880. 69 Average value $865. 81 Positive values 609 records with value of $527, 277. 55 Negative values (probably credit notes) 161 records with value of $(58, 396. 86)

Stratify (數值分類)

Workshop C 1 • 開啟ap_trans • 執行Analyze/Stratify • 若先執行Statistic則Min及Max會自動帶出 • 請問Invoice. Amount 高於 10, 000元之筆數共有幾筆 • 區分0, 1000, 3000, 5000, 7000之區間

Analysing data characteristics Non-numeric fields Pw. C

Analysing data characteristics (非數值) • Classify • Age • Summarise

Analysing data characteristics • Using these commands we could: • Group data into subsets and return relevant totals for these subsets: --> Group data in a payroll file into departments and return the total payroll value for each department; • Perform ageing of records with date fields: --> Age a receivables file by intervals of 30 days to determine bad and doubtful debts.

Classify

Age • Performs ageing of records based upon a selected date field; • 可自行設定Cut - Off 的 日期，且可自訂區間

Age

Summarize Summarise • The order that the Summarise On fields are selected is very important

Summarize