Calculating percentage change month over month

Analytics
Highlighted
Enthusiast

Calculating percentage change month over month

Hello Forum, 

I'm trying to calculate the percentage change month over month for the calculated field (TXN_1000_SUM) below but when I use the MAX(TXN_1000_SUM......) line below at the bottom nothing returns.  It should calculate the difference in the aggregated sum for TXN_1000_SUM from 6/1/2018 to 5/1/2018, 5/1/2018 to 4/1/2018, etc.  Also, what would be a good format to use to yield something like ex. 10.0%?  Forgive me if this is overly simple; this is my first OLAP query and I couldn't find an example of this.  Many thanks in advance for your help!

 

SELECT 
	 COMPLETE_DT - EXTRACT(DAY FROM COMPLETE_DT) +1 AS PERIOD_DT						
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 0 AND 1000 THEN 1 ELSE 0 END)							AS TXN_1000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 0 AND 1000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18,0)) ELSE 0 END)		AS TXN_1000_SUM
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 1000	AND 2000 THEN 1 ELSE 0 END)						AS TXN_2000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 1000	AND 2000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18,0)) ELSE 0 END)	AS TXN_2000_SUM
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 2000	AND 3000 THEN 1 ELSE 0 END)						AS TXN_3000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 2000	AND 3000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18,0)) ELSE 0 END)	AS TXN_3000_SUM
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 3000	AND 4000 THEN 1 ELSE 0 END)						AS TXN_4000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 3000	AND 4000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18,0)) ELSE 0 END)	AS TXN_4000_SUM
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 4000	AND 5000 THEN 1 ELSE 0 END)						AS TXN_5000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 4000	AND 5000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18,0)) ELSE 0 END)	AS TXN_5000_SUM
	
	,MAX(TXN_1000_SUM) OVER (PARTITION BY TXN_1000_SUM ORDER BY PERIOD_DT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS PREV_AMT
	,TXN_1000_SUM - PREV_AMT AS PCT_CHNG
	
	
FROM    TABLE1 	AS T1

WHERE PERIOD_DT BETWEEN ADD_MONTHS(CAST('2018-07-01' AS DATE),-13) AND CAST('2018-07-01' AS DATE) - 1 

GROUP BY 1
ORDER BY 1;

 

-- RedMittens 


Accepted Solutions
Teradata Employee

Re: Calculating percentage change month over month

I think you need to use a derived table here - at least it's easier to understand this way.  Also, this makes it easier to add more stats - for TXN_2000, TXN_3000, etc.

 

SELECT PERIOD_DT
	,TXN_1000_SUM
	,MAX(TXN_1000_SUM) OVER (ORDER BY PERIOD_DT ROWS BETWEEN 1 PRECEDING AND  1 PRECEDING) AS PREV_AMT
	,Cast(100*(TXN_1000_SUM - PREV_AMT)/Coalesce(PREV_AMT,TXN_1000_SUM) as Dec(5,2)) || "%" AS PCT_CHNG
FROM (
  SELECT COMPLETE_DT - EXTRACT(DAY FROM COMPLETE_DT) +1 AS PERIOD_DT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 0 AND  1000 THEN 1 ELSE 0 END) AS TXN_1000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 0 AND  1000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18 ,0)) ELSE 0 END) AS TXN_1000_SUM
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 1000 AND  2000 THEN 1 ELSE 0 END) AS TXN_2000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 1000 AND  2000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18 ,0)) ELSE 0 END) AS TXN_2000_SUM
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 2000 AND  3000 THEN 1 ELSE 0 END) AS TXN_3000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 2000 AND  3000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18 ,0)) ELSE 0 END) AS TXN_3000_SUM
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 3000 AND  4000 THEN 1 ELSE 0 END) AS TXN_4000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 3000 AND  4000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18 ,0)) ELSE 0 END) AS TXN_4000_SUM
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 4000 AND  5000 THEN 1 ELSE 0 END) AS TXN_5000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 4000 AND  5000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18 ,0)) ELSE 0 END) AS TXN_5000_SUM
  FROM TABLE1 AS T1
  WHERE PERIOD_DT BETWEEN ADD_MONTHS(CAST('2018-07-01' AS DATE) ,-13) AND  CAST('2018-07-01' AS DATE) - 1
  GROUP BY 1
) SumTxn
ORDER BY 1;
1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: Calculating percentage change month over month

I think you need to use a derived table here - at least it's easier to understand this way.  Also, this makes it easier to add more stats - for TXN_2000, TXN_3000, etc.

 

SELECT PERIOD_DT
	,TXN_1000_SUM
	,MAX(TXN_1000_SUM) OVER (ORDER BY PERIOD_DT ROWS BETWEEN 1 PRECEDING AND  1 PRECEDING) AS PREV_AMT
	,Cast(100*(TXN_1000_SUM - PREV_AMT)/Coalesce(PREV_AMT,TXN_1000_SUM) as Dec(5,2)) || "%" AS PCT_CHNG
FROM (
  SELECT COMPLETE_DT - EXTRACT(DAY FROM COMPLETE_DT) +1 AS PERIOD_DT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 0 AND  1000 THEN 1 ELSE 0 END) AS TXN_1000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 0 AND  1000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18 ,0)) ELSE 0 END) AS TXN_1000_SUM
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 1000 AND  2000 THEN 1 ELSE 0 END) AS TXN_2000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 1000 AND  2000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18 ,0)) ELSE 0 END) AS TXN_2000_SUM
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 2000 AND  3000 THEN 1 ELSE 0 END) AS TXN_3000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 2000 AND  3000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18 ,0)) ELSE 0 END) AS TXN_3000_SUM
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 3000 AND  4000 THEN 1 ELSE 0 END) AS TXN_4000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 3000 AND  4000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18 ,0)) ELSE 0 END) AS TXN_4000_SUM
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 4000 AND  5000 THEN 1 ELSE 0 END) AS TXN_5000_CNT
	,SUM(CASE WHEN (RECV_AMT/EXCH_RT) BETWEEN 4000 AND  5000 THEN CAST((RECV_AMT/EXCH_RT) AS DECIMAL(18 ,0)) ELSE 0 END) AS TXN_5000_SUM
  FROM TABLE1 AS T1
  WHERE PERIOD_DT BETWEEN ADD_MONTHS(CAST('2018-07-01' AS DATE) ,-13) AND  CAST('2018-07-01' AS DATE) - 1
  GROUP BY 1
) SumTxn
ORDER BY 1;
Enthusiast

Re: Calculating percentage change month over month

Thank you so much GJColeman for your help.  I really appreciate it! :)

 

- RedMittens