September 9, 2014 at 4:24 am
I'm having a rounding issue I was hoping some of the minds here would be able to solve.
Although I think its a simple rounding issue here is some rather verbose background on the business logic to help understand what I'm trying to achieve.
When sizing products we use predefined size groups that the users can choose any or all of the sizes from. For example if i size group consisted of sizes (6,8,10) they could use all sizes (6,8,10) or just (6,8) or just (10) if required. Similarly, if a group consisted of (S,M,L,XL) they could choose to only buy (S,L). They cannot choose across groups, so would not be able to choose (6,S)
Once the required sizing is determined they then assign size mixes to the sizes to denote how much of the buy will be in that size. So for example if we had 3 sizes: (6,8,10) and they had the associated mixes (25%,25%,50%) that would mean we would buy 25% of size 6 and 50% of size 10. All size mixes must add up to 100% in total.
The users do analysis to determine what sizes they wish to buy and how much of it.
We also have a franchise portion of the business that have some predefined size mixes. They use the same base size groups as above, but the rule is that they can only use sizes that the particular product is being bought in.
So if the assigned franchise mix is S (50%), M (50%) and the main mix was S (100%) then the franchise mix would only be able to then have the S size.
We would then eliminate the sizes from the franchise mix and then to ensure that the franchise mix still adds to 100 we would then pro-rate up the franchise mix to give a new mix. To do this I divide one by the total the remaining size mixes to get a ratio and then multiple the mixes by this factor.
In the case above we not not be able to use the M size and would only use the S.This would be
-Total of remaining mixes, in this case only size S for simplicity
1 / 0.5 = 2
-multiple original mix by this factor
0.5 * 2 = 1
size S would now be 100% instead of 50%
The issue I'm having is that on occasion some of the totals are adding up to 100.01% because another one of the requirements is that it needs to be 4 decimal places (0.1015 would represent 10.15% in excel)
Here is a shortened version of the code with some test data:
DECLARE @SizeRange TABLE
(
SizeProfileIDINTNOT NULL,
CPCVARCHAR(3)NOT NULL,
SizeNameVARCHAR(5)NOT NULL,
SizeMixDECIMAL(14,4)NOT NULL
);
DECLARE @FranchiseSizeRange TABLE
(
CPCVARCHAR(3)NOT NULL,
SizeNameVARCHAR(5)NOT NULL,
SizeMixDECIMAL(14,4)NOT NULL
);
INSERT INTO @SizeRange (SizeProfileID,CPC,SizeName,SizeMix)
VALUES(1,'ROI','6',0.0500),
(1,'ROI','8',0.0500),
(1,'ROI','10',0.0000),
(1,'ROI','12',0.0000),
(1,'ROI','14',0.0000),
(1,'ROI','16',0.5000),
(1,'ROI','18',0.3000),
(1,'ROI','20',0.1000),
(1,'ROI','22',0.0000),
(1,'UK','6',0.0500),
(1,'UK','8',0.0500),
(1,'UK','10',0.0000),
(1,'UK','12',0.0000),
(1,'UK','14',0.0000),
(1,'UK','16',0.5000),
(1,'UK','18',0.3000),
(1,'UK','20',0.1000),
(1,'UK','22',0.0000);
INSERT INTO @FranchiseSizeRange (CPC,SizeName,SizeMix)
VALUES('A','6',0.0900),
('A','8',0.1500),
('A','10',0.1600),
('A','12',0.1700),
('A','14',0.1300),
('A','16',0.1200),
('A','18',0.1000),
('A','20',0.0600),
('A','22',0.0200),
('B','6',0.1400),
('B','8',0.1500),
('B','10',0.1800),
('B','12',0.1800),
('B','14',0.1400),
('B','16',0.1200),
('B','18',0.0900),
('B','20',0.0000),
('B','22',0.0000);
;WITH UsedSizes (SizeName)
AS
(
SELECTSR.SizeName
FROM@SizeRange AS SR
WHERESR.SizeProfileID = 1
--eliminate sizes that have not been given a mix
AND SR.SizeMix > 0
GROUPBY SR.SizeName
),FranchiseSizesAndFactor (FranchisePartner,Size,Ratio)
AS
(
SELECTF.CPC,
F.SizeName,
F.SizeMix * 1.0 / SUM(F.SizeMix) OVER (PARTITION BY F.CPC)
FROM@FranchiseSizeRange AS F
INNER
JOINUsedSizes AS S
ONS.SizeName = F.SizeName
WHEREF.SizeMix > 0
)
SELECTF.FranchisePartner,
F.Size,
F.Ratio,
--different versions of it, partner A is not 100%, B is
ROUND(F.Ratio,4),
CAST(F.Ratio AS DECIMAL(14,4)),
CAST(F.Ratio AS DECIMAL(14,5))
FROMFranchiseSizesAndFactor AS F
Any ideas on how to get this to round to 4dp and still total 100%?
September 9, 2014 at 5:18 am
This is called "allocation issue".
You need to correct/adjust your calculated ratio so it will sum up to 100%
The simplest way is to chose which single item ratio you would want to adjust. The following code will adjust the item with minimal size:
;WITH UsedSizes (SizeName)
AS
(
SELECTSR.SizeName
FROM@SizeRange AS SR
WHERESR.SizeProfileID = 1
--eliminate sizes that have not been given a mix
AND SR.SizeMix > 0
GROUPBY SR.SizeName
),FranchiseSizesAndFactor (FranchisePartner,Size,Ratio)
AS
(
SELECTF.CPC,
F.SizeName,
ROUND(F.SizeMix * 1.0 / SUM(F.SizeMix) OVER (PARTITION BY F.CPC) ,4)
FROM@FranchiseSizeRange AS F
INNER
JOINUsedSizes AS S
ONS.SizeName = F.SizeName
WHEREF.SizeMix > 0
)
SELECTF.FranchisePartner,
F.Size,
F.Ratio,
SUM(F.Ratio) OVER (PARTITION BY F.FranchisePartner) AS NotAdjustedRatioSUM,
A.Adjustment AS RatioAdjustment,
F.Ratio + ISNULL(A.Adjustment,0) AS AdjustedRatio,
SUM(F.Ratio + ISNULL(A.Adjustment,0)) OVER (PARTITION BY F.FranchisePartner) AS AdjustedRatioSUM
FROMFranchiseSizesAndFactor AS F
LEFT JOIN (SELECT FranchisePartner, MIN(Size) AS AdjSize, 1.0 - SUM(Ratio) AS Adjustment FROM FranchiseSizesAndFactor GROUP BY FranchisePartner) AS A
ON A.FranchisePartner = F.FranchisePartner AND A.AdjSize = F.Size
September 9, 2014 at 5:31 am
Fantastic stuff Eugene.
I'd be playing around with something along these lines, but hadn't quite got to this yet.
Thanks for the help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply