July 30, 2009 at 9:59 am
Hi i've a margin report that i have developed in sql but i need to be able to calculate the margin at different levels depending on how i drill it.
What i've done so far is just calculate the margin at line level
but how do i get to to roll up and calculate correctly.
here is my expected results.
i have Qty Cost Net Sale Margin all money data types
Prod Hier, is Impulse, Soft Drinks, 2 LTR BOTTLE MINERALS, product code and then dowen to invoice level, the line level detail is calculated ok, it just when i rol it up i can get it to recalculate for the different Prod Hier,
Values
Prod Hier Qty Cost Net Sale Margin
Impulse 2 11.74 17.08 54.13 wrong
Soft Drinks 2 11.74 17.08 54.13 wrong
2 LTR BOTTLE MINERALS 2 11.74 17.08 54.13 wrong
product 100270 2 11.74 17.08 54.13 wrong
inv_code 31757 1 7.5 11.99 37.44 ok
inv_code 33320 1 4.24 5.09 16.69 ok
Grand Total 2 11.74 17.08 54.13
Whats it should be like when calculated.
Values
Prod Hier Qty Cost Net Sale Margin
Impulse 2 11.74 17.08 31.25 ok
Soft Drinks 2 11.74 17.08 31.25 ok
2 LTR BOTTLE MINERALS 2 11.74 17.08 31.25 ok
product 100270 2 11.74 17.08 31.25 ok
inv_code 31757 1 7.5 11.99 37.44 ok
inv_code 33320 1 4.24 5.09 16.69 ok
Grand Total 2 11.74 17.08 31.25 ok
July 30, 2009 at 10:59 am
I'm not sure how your margin is calculated, but it sounds like you have an issue with a semi-additive or non-additive aggregation. For instance, if you have a formula that's (x-y)/y, to get the aggregate, you can't simply sum those lower results with Sum( (x-y)/y ), because it's a non-additive measure. You have to do something like Sum(x-y)/Sum(y).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 30, 2009 at 12:54 pm
For a more definitive answer (and a coded, tested solution), please give us the margin calculation formula and set up your sample data as shown in this best practices[/url] article.
Thanks.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 31, 2009 at 4:57 am
Hi when i display this in in a pivot table it seems to add up the margin figures in the total how do i get them to recalculate again ?,
at the highest level of the grouping before i drill down into invoice_no
the results should be
cost net_sale margin
25.0034.97 28.51
---Here is my code
drop table #tmp1
-- create the table
create table #tmp1
(
div_descvarchar(50),
prod_groupvarchar(50),
costmoney,
net_salemoney,
marginmoney
)
-- insert the data into the table
insertinto #tmp1(div_desc,prod_group,cost,net_sale)
select 'soft drinks','non mix',7.50,11.99 union all
select 'soft drinks','non mix',8,10.99 union all
select'soft drinks','non mix',9.50,11.99
-- check data is inserted
select *
from#tmp1
-- calculate the margin on line level
update#tmp1
setmargin = ((net_sale - cost) / net_sale)* 100
-- check your margin is calculated ok
select *
from#tmp1
/*
soft drinksnon mix7.5011.9937.44
soft drinksnon mix8.0010.9927.20
soft drinksnon mix9.5011.9920.76
*/
-- calculate the sum on the margin so the cost and the net_sales
-- should be added up and then the sum of them values should be
-- used to calculate the new total margin
selectsum(cost) as cost,
sum(net_sale) as net_sale,
((sum(net_sale) - sum(cost)) / sum(net_sale))* 100 as margin
from#tmp1
group bydiv_desc,prod_group
-- Works fine in sql but when displayed in a pivot table its adding the detail line margin together given me 54.13 as its adding the 16.69 and 37.44 together to get the 54.13
July 31, 2009 at 4:36 pm
A pivot table in what software? Excel?
You need to code the margin as a calculated member in your pivot table software, not in SQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 4, 2009 at 2:56 am
I applied the calculations in the cube design and it work fine for me..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply