Forum Discussion

Copper Contributor

Nov 29, 2024

Solved

Help in Query to calculate the % from last two timelines

I want to compare the FAmt from last recent two lines and if difference is more than 80% then it should be in the result. The group/index keys are A,B,C,D, Edate. Create table #AR (A int, B char(10)...

Analysis Services

analytics

rodgerkong

Dec 04, 2024

Check this code and comments

SELECT A,B,C,D,CAST(Edate AS DATE) AS EDate,FAmt,G, 'Current Amount is > 80% from last timeline' AS Comments FROM
(
	SELECT A,B,C,D,Edate,FAmt,G,
		IIF
		(
			LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) = 0.00 AND FAmt <> 0.00, --If previous value is ZERO and the last one is not, the difference can be considered as >80%
			999999, 
			IIF
			(
				LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) = 0.00 AND FAmt = 0.00, --If previous value and the last one both ZERO, the difference is ZERO
				0,
				FAmt/LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) - 1  --OR calculate as normal way
			)
		) 
		AS P,
		ROW_NUMBER() OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) AS RN
	FROM #AR
)T
WHERE
	P>0.8 AND RN = 1

Kenny_Gua

Copper Contributor

Dec 03, 2024

if FAmt's difference is more than 80% of very latest last two timelines then it should be in the result. If difference is less than 80% then it should not be in the result. Here only problem is that Query is no executing successfully. It is getting error. Error: Divide by Zero error encountered.

rodgerkong

Iron Contributor

Dec 04, 2024

Check this code and comments

SELECT A,B,C,D,CAST(Edate AS DATE) AS EDate,FAmt,G, 'Current Amount is > 80% from last timeline' AS Comments FROM
(
	SELECT A,B,C,D,Edate,FAmt,G,
		IIF
		(
			LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) = 0.00 AND FAmt <> 0.00, --If previous value is ZERO and the last one is not, the difference can be considered as >80%
			999999, 
			IIF
			(
				LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) = 0.00 AND FAmt = 0.00, --If previous value and the last one both ZERO, the difference is ZERO
				0,
				FAmt/LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) - 1  --OR calculate as normal way
			)
		) 
		AS P,
		ROW_NUMBER() OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) AS RN
	FROM #AR
)T
WHERE
	P>0.8 AND RN = 1

Kenny_Gua
Copper Contributor
Dec 06, 2024
Hi, Thanks a lot for your help