Divide by zero on Table transformer
Hi I'm trying to calculate growth over year. As you can see below yellow highlighted, product B has "zero divide by number" case and growth is shown as "infinity". How to get this shown as 100%. ??
My Table transformer Query is as below.
============================
SELECT *,
ROUND(((T1.'Count FY2020' - T1.'Count FY2019')/T1.'Count FY2019')*100, 2) AS 'Growth, %'
FROM T1
============================
1 answer
1 accepted
Katerina Kovriga {Stiltsoft}
Rising Star 
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Hi @emagnun ,
Please try the following:
SELECT T1.'Product Type', T1.'Count FY2019', T1.'Count FY2020',
CASE
WHEN ROUND((T1.'Count FY2020'*100/T1.'Count FY2019' - 100), 2) LIKE "Infinity"
THEN "100"
ELSE ROUND((T1.'Count FY2020'*100/T1.'Count FY2019' - 100), 2)
END
AS 'Growth, %'
FROM T*
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.