1, What is PCP?
Prior Comparable Period
2, Why needs PCP?
This is to compare the value with history value.
3, How to derive the PCP value?
The simple module is:
SELECT
COALESCE(CURR.REPORT_DATE,ADD_MONTHS(PCP.REPORT_DATE, 1)) AS REPORT_DATE
,COALESCE(CURR.D1 , PCP.D1 ) AS D1 –This is dimensions
,COALESCE(CURR.D2 , PCP.D2 ) AS D2
,CURR.M1 –This is measures
,PCP.M1 AS PCP_M1
FROM PCP_TEST CURR
FULL JOIN PCP_TEST PCP
ON ADD_MONTHS(CURR.REPORT_DATE, -1) = PCP.REPORT_DATE
AND COALESCE (CURR.D1,’N/A’) = COALESCE (PCP.D1, ‘N/A’)
AND COALESCE (CURR.D2,’N/A’) = COALESCE (PCP.D2, ‘N/A’)
;
4, How to handle NULL values?
COALESCE (CURR.D2,’N/A’) could be used in the join condition
5, When calculating for PCP values, the granularity of the table should be the combination of all the dimensions
6, How many rows before the PCP and after the PCP?
7, How to process NULL value if no data after PCP join