June 27, 2020 at 11:09 am
I would be grateful if you can help me to calculate the RunRate(forecast) on the following table .
The run rate will be calculate only on the future months on the field 'im_actual' , and the completed months (Past_Months) the value of 'Im_actual' will be same.
The field 'int_period' keep Fiscal months , so the completed month are , Apr(1) and May(2) where , June(3) onward are future month where we need to calcuatel ForeCast/RunRaate,
The completed month we don't need to calcuate and the 'Im_actual' values for completed will be same as exist in the table/field(im_actual) ,
To calculate the Forecast/RR for future months. it should sum of the completed months so in this case will be i.e. (1 and 2) and divide by 2(last completed month), so the calculate valurs for month (3 to 12) will be constant values.
Similarly, if I wants to calculate the Fore-cast after the month June(3) completed , the future Forecast will be sum of (month [1 to 3] and divided by 3 and this same/constact values will show for furtre moths 4 to 12..
The dummy data set script i've copied below
I've tried using recursive sql and UNION by split into two data set
1st data set simple where the 'im_actual' value not calculated by using
Also attached my sample
Create tableForecast_Test
(
int_period integer
,Division VARCHAR(10)
,Cost_Centre integer
,im_actual integer);
INSERT INTO Forecast_Test values (1 , 'A and M', 12345, 1000)
INSERT INTO Forecast_Test values (2 , 'A and M', 12345, 800)
INSERT INTO Forecast_Test values (3 , 'A and M', 12345, 600)
INSERT INTO Forecast_Test values (4 , 'A and M', 12345, 700)
INSERT INTO Forecast_Test values (5 , 'A and M', 12345, 1200)
INSERT INTO Forecast_Test values (6 , 'A and M', 12345, 1000)
INSERT INTO Forecast_Test values (1 , 'A and M', 7777, 500)
INSERT INTO Forecast_Test values (2 , 'A and M', 7777, 400)
INSERT INTO Forecast_Test values (3 , 'A and M', 7777, 600)
INSERT INTO Forecast_Test values (4 , 'A and M', 7777, 700)
INSERT INTO Forecast_Test values (5 , 'A and M', 7777, 1200)
INSERT INTO Forecast_Test values (6 , 'A and M', 8888, 1000)
SELECT
int_period, Division, Cost_Centre,SUM (im_actual) OVER (ORDER BY int_period) AS RunningAgeTotal,
--AVG (StudentAge) OVER (ORDER BY Id) AS RunningAgeAverage
FROM Forecast_Test
WITH
cte_main AS
(
--Part Two (sum above query)
SELECT 'Total Completed Month' as QueryNo, 99 as int_period, d1.Division, d1.Cost_Centre,0 as
im_actual, sum(d1.im_actual) as Sum_Actual
FROM Forecast_Test d1--inner join Forecast_Test m on m.int_period = d1.int_period and m.Division =
d1.Division and m.Cost_Centre = d1.Cost_Centrewhere d1.Cost_Centre=12345and d1.int_period <=2group by
d1.Division, d1.Cost_Centre
--Part Three (--Future Month replace Sum_Actual)
UNION ALL
SELECT d2.int_period, d2.Division, d2.Cost_Centre, d2.im_actual, d1.sum_Actual
FROM Forecast_Test d2
inner join Forecast_Test d1 on d2.Cost_Centre= d1.Cost_Centre and d2.Division= d1.Divisionwhere
d2.Cost_Centre=12345 and d2.int_period >2
)
SELECT 'Completed Month' as QueryNo, int_period, Division, Cost_Centre,im_actual, 0 as Sum_ActualFROM Forecast_Testwhere Cost_Centre=12345and int_period <=2UNION ALLselect * from cte_main
June 27, 2020 at 11:14 am
Create table
Forecast_Test
(
int_period integer,
Division VARCHAR(10),
Cost_Centre integer,
im_actual integer
);
INSERT INTO Forecast_Test values (1 , 'A and M', 12345, 1000)
INSERT INTO Forecast_Test values (2 , 'A and M', 12345, 800)
INSERT INTO Forecast_Test values (3 , 'A and M', 12345, 600)
INSERT INTO Forecast_Test values (4 , 'A and M', 12345, 700)
INSERT INTO Forecast_Test values (5 , 'A and M', 12345, 1200)
INSERT INTO Forecast_Test values (6 , 'A and M', 12345, 1000)
--
INSERT INTO Forecast_Test values (1 , 'A and M', 7777, 500)
INSERT INTO Forecast_Test values (2 , 'A and M', 7777, 400)
INSERT INTO Forecast_Test values (3 , 'A and M', 7777, 600)
INSERT INTO Forecast_Test values (4 , 'A and M', 7777, 700)
INSERT INTO Forecast_Test values (5 , 'A and M', 7777, 1200)
INSERT INTO Forecast_Test values (6 , 'A and M', 8888, 1000)
SELECT int_period, Division, Cost_Centre,
SUM (im_actual) OVER (ORDER BY int_period) AS RunningAgeTotal,
--AVG (StudentAge) OVER (ORDER BY Id) AS RunningAgeAverage
FROM Forecast_Test
[/quote]
Thanks Again , if you have any question please feel free to ask me
Many thanks
Farhan
Also ried
WITH cte_main AS
(
--Part Two (sum above query)
SELECT 'Total Completed Month' as QueryNo, 99 as int_period, d1.Division, d1.Cost_Centre,
0 as im_actual, sum(d1.im_actual) as Sum_Actual
FROM Forecast_Test d1
--inner join Forecast_Test m on m.int_period = d1.int_period and m.Division = d1.Division and m.Cost_Centre = d1.Cost_Centre
where d1.Cost_Centre=12345
and d1.int_period <=2
group by d1.Division, d1.Cost_Centre
--Part Three (--Future Month replace Sum_Actual)
UNION ALL
SELECT d2.int_period, d2.Division, d2.Cost_Centre, d2.im_actual, d1.sum_Actual
FROM Forecast_Test d2
inner join Forecast_Test d1 on d2.Cost_Centre= d1.Cost_Centre and d2.Division= d1.Division
where d2.Cost_Centre=12345 and d2.int_period >2
)
SELECT 'Completed Month' as QueryNo, int_period, Division, Cost_Centre,
im_actual, 0 as Sum_Actual
FROM Forecast_Test
where Cost_Centre=12345
and int_period <=2
UNION ALL
select * from cte_main
June 29, 2020 at 5:22 pm
My only question with this is a pretty easy one - should your calculation to calculate the Forecast/RR for future months be per-customer, per Cost_Centre, or only look at the int_period?
Basically, I am just trying to find the proper way to group it across cost centres and across divisions, or if it even needs to be. It just changes a small part of the math, not a lot.
I am also not sure I fully understand the math on this, so doing a "best guess" and hoping I am close or at least pointing you in the right direction. The code does have a "magic number" in it (getting the current month and subtracting 3), but this is based on you saying that month 1 is April and DATEPART(month,'01-04-2020') will give me 4 as the month is 4. So subtracting 3 from the current month will give me a number between -2 and 9 (a 12 digit range). Later when we do the check, we are adding 12 to it if the value is less than 1 (ie 0 becomes 12, -1 becomes 11 and -2 becomes 10) so we can get the full fiscal year. So if the current calendar month is January, you would get -2. Your Fiscal month for January should be 10 (as you have 3 months remaining until year end - January, February and March) so the math here works even if it is not that pretty.
My current guess as to how to tackle this would be something similar to:
WITH [sourceData]
AS
(
SELECT
[int_period]
, [Division]
, [Cost_Centre]
, [im_actual]
, SUM([im_actual]) OVER (PARTITION BY
[Cost_Centre]
ORDER BY [int_period]
) AS [RunningAgeTotal]
, DATEPART( MONTH
, GETDATE()
) - 3 AS [currentFiscal]
FROM[#Forecast_Test]
)
, [ForecastRR]
AS
(
SELECT
[sourceData].[Cost_Centre]
, (SUM( CASE
WHEN [sourceData].[int_period] < CASE
WHEN [sourceData].[currentFiscal] < 1
THEN [sourceData].[currentFiscal] + 12
ELSE [sourceData].[currentFiscal]
END
THEN [sourceData].[im_actual]
ELSE 0
END
)
) / ([sourceData].[currentFiscal] - 1) AS [forecastRR]
FROM[sourceData]
GROUP BY[sourceData].[Cost_Centre]
, [sourceData].[currentFiscal]
)
SELECT
[sourceData].[int_period]
, [sourceData].[Division]
, [sourceData].[Cost_Centre]
, [sourceData].[im_actual]
, [sourceData].[RunningAgeTotal]
, [ForecastRR].[forecastRR]
, CASE
WHEN [sourceData].[int_period] < [sourceData].[currentFiscal]
THEN [sourceData].[im_actual]
ELSE [ForecastRR].[forecastRR]
END AS [im_Actual_with_ForecastRR]
FROM[sourceData]
JOIN[ForecastRR]
ON [ForecastRR].[Cost_Centre] = [sourceData].[Cost_Centre];
Is the above close to what you were looking for?
If not, could you post a sample of what you expect the output to look like in Excel or similar? This will help me figure out what you expect vs what I provided.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply