April 3, 2019 at 11:43 pm
Hello community,
Suppose i want to Calculate percentage per family product regarding the total of sales:
Example:
Family totalinvoice cost profit Margin Percent
acessories 10000 5000 5000 50% (a)
material 200 50 150 25% (b)
Total sales 10200
I want to do the following calculation :
( acessories (10000) / total sales (10200) ) * 100 in (a) the value is : 98,03922
(Material (200)/ total sales (10200) ) * 100 in (b) the value is : 1,960784314
I have build this simple CTE, the problem is how to do the calculation of percent by family.
;WITH CTE
as(
select DISTINCT ft.ccusto,
sum(ft.ETTILIQ) OVER(partition by ft.ccusto order by ft.ccusto) [Total Sales],
SUM(ettiliq-ECUSTO) OVER (partition by ft.ccusto order by ft.ccusto) [Profit],
SUM((ettiliq-ECUSTO)) OVER (partition by ft.ccusto order by ft.ccusto) /sum(ft.ETTILIQ) OVER(partition by ft.ccusto order by ft.ccusto) * 100 [Margin]
FROM FT WHERE FDATA BETWEEN '20190101' AND '20191231' and ccusto <> ''
union all
SELECT 'total',
sum(ft.ETTILIQ)[total] ,0,0
FROM FT WHERE FDATA BETWEEN '20190101' AND '20191231' and ccusto <> ''
)
SELECT * FROM cte
Someone could give a solution to do this.
Thanks,
Best regards,
Luis
April 4, 2019 at 9:38 am
Hello community,
maybe to understand better, what I need is to return the grand total for each line independently of the family.
Do you have any suggestions for doing this?
Thanks,
Luis
April 4, 2019 at 9:58 am
Hello communty,
Thanks but i solve my problem like this :
;WITH CTE
as(
select distinct ft.ccusto,
sum(ft.ETTILIQ) OVER(partition by ft.ccusto order by ft.ccusto) [Total],
SUM(ettiliq-ECUSTO) OVER (partition by ft.ccusto order by ft.ccusto) [Profit],
SUM((ettiliq-ECUSTO)) OVER (partition by ft.ccusto order by ft.ccusto) /sum(ft.ETTILIQ) OVER(partition by ft.ccusto order by ft.ccusto) * 100 [Margem]
FROM FT WHERE FDATA BETWEEN '20190101' AND '20191231' and ccusto <> ''
)
SELECT distinct cc.ccusto , cc.total, cc.profit, cc.margem , x.TotalSales--/NULLIF(cc.Total,0) * 100 [total]
FROM
(SELECT ft.ccusto, sum(ft.ETTILIQ) OVER()[TotalSales]
FROM ft WHERE FDATA BETWEEN '20190101' AND '20191231' and ccusto <> '') x LEFT join
cte cc ON cc.ccusto = x.ccusto
Best regards,
Luis
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply