Have a question on this - my plan to fill the future weeks is to use previous years WoW movement (so I can get a full year forecast) - generally this works well for the business so am using this.
What I want to do is use the below as the actual revenue numbers:
And use the week on week movements from the year before to fill the future weeks so for example:
So I want to multiply the actual for week 4 by the week 5 multiple (1.125) to get a forecast value for week 5 (15,187.5), then for week 6 multiply 15,187.5 by 0.972 up to week 8:
Anyone got any ideas for code to do this?
Thanks
CodePudding user response：
You could use the model clause for this. There might be a more efficient/cleaner way, but assuming you have a table with year, week and revenue then this:
select year, week, revenue
from your_table
model
dimension by (year, week)
measures (revenue, 0 week_chg)
rules upsert sequential order (
week_chg[any, any] =
revenue[cv(year) - 1, cv(week)]/revenue[cv(year) - 1, cv(week) - 1],
revenue[any, any] =
coalesce(
revenue[cv(), cv()],
round(revenue[cv(year), cv(week) - 1] * week_chg[cv(year), cv(week)], 2)
)
)
produces:
| YEAR | WEEK | REVENUE |
|---|---|---|
| 2021 | 1 | 1000 |
| 2021 | 2 | 2000 |
| 2021 | 3 | 3000 |
| 2021 | 4 | 8000 |
| 2021 | 5 | 9000 |
| 2021 | 6 | 8750 |
| 2021 | 7 | 9500 |
| 2021 | 8 | 10000 |
| 2022 | 1 | 10000 |
| 2022 | 2 | 12000 |
| 2022 | 3 | 15000 |
| 2022 | 4 | 13500 |
| 2022 | 5 | 15187.5 |
| 2022 | 6 | 14765.63 |
| 2022 | 7 | 16031.26 |
| 2022 | 8 | 16875.01 |
This applies two rules, in sequence. The first:
week_chg[any, any] =
revenue[cv(year) - 1, cv(week)]/revenue[cv(year) - 1, cv(week) - 1]
works out the week change value from the previous year - the revenue from one year earlier and the same week, divided by the revenue from one year earlier and one week earlier.
Then
revenue[any, any] =
coalesce(
revenue[cv(), cv()],
round(revenue[cv(year), cv(week) - 1] * week_chg[cv(year), cv(week)], 2)
sets the revenue for the current dimension to either the real revenue value if there is; otherwise is multiples the revenue from the previous week (which, crucially, it may just have calculated itself) by the matching week change value. And then it rounds that value to two decimal places, which seems to be what you want.
(The last two results are very slightly different to your example because of rounding differences. If week_chg is rounded to 6 decimal places then the 2002 week 8 comes out as exactly 16875, but weeks 7 and 6 are then different.)
The fiddle has an intermediate result that shows both values, if you need them separately.
CodePudding user response：
Assuming you have the tables:
CREATE TABLE this_year (week, revenue) AS
SELECT 1, 10000 FROM DUAL UNION ALL
SELECT 2, 12000 FROM DUAL UNION ALL
SELECT 3, 15000 FROM DUAL UNION ALL
SELECT 4, 13500 FROM DUAL;
CREATE TABLE last_year (week, revenue) AS
SELECT 1, 5000 FROM DUAL UNION ALL
SELECT 2, 6000 FROM DUAL UNION ALL
SELECT 3, 7000 FROM DUAL UNION ALL
SELECT 4, 8000 FROM DUAL UNION ALL
SELECT 5, 9000 FROM DUAL UNION ALL
SELECT 6, 8750 FROM DUAL UNION ALL
SELECT 7, 9500 FROM DUAL UNION ALL
SELECT 8, 10000 FROM DUAL;
Then you can use a MODEL clause:
SELECT week,
revenue
FROM (
SELECT ly.week AS week,
ly.revenue AS ly_revenue,
ty.revenue AS ty_revenue
FROM last_year ly
LEFT OUTER JOIN this_year ty
ON (ly.week = ty.week)
)
MODEL
DIMENSION BY (week)
MEASURES (
ly_revenue,
ty_revenue,
0 AS ly_multiplier,
0 AS revenue
)
RULES AUTOMATIC ORDER (
revenue[1] = COALESCE(
ty_revenue[1],
ly_revenue[1]
),
revenue[week>1] = COALESCE(
ty_revenue[cv()],
revenue[cv()-1] * ly_revenue[cv()]/ly_revenue[cv()-1]
)
);
Which outputs:
WEEK REVENUE 1 10000 2 12000 3 15000 4 13500 6 14765.625 5 15187.5 7 16031.25 8 16875
db<>fiddle here
CodePudding user response：
Firstly please have a look the weekly change numbers of last year. When you multiply week 4 revenue 8000 x with the week5 change 1.125 = 9000 you will get the week 5 revenue. When you multiply changes of week5 and week6 = 1.125 x 0.972222 = 1,093749. Then when you multiply this number with week 4 revenue 8000 = then you will get week 6 revenue 8750.
So smilar logic, you need to keep latest record which you know. So in this case it is week 4 for this year. Then multiply weekly changes values. To do that lets get help from math. you can sum the logarithm of each value, then take the the exponential of the result. Then you got the multiplied changed values for each week then multiply result with revenue.
select *
from (
select '1' as week, 10000 as revenue
union all
select '2' as week, 12000 as revenue
union all
select '3' as week, 15000 as revenue
union all
select '4' as week, 13500 as revenue
)m
union all
-- future
select
ly.week,
(m.revenue * exp(sum(log(ly2.week_chg)))) as revenue_future
from (
select '4' as week, 13500 as revenue -- you can get latest record via rownum, i will share query below
)m
join (
select '4' as week, 8000 as revenue_ly, null as week_chg
union all
select '5' as week, 9000 as revenue_ly, 1.125 as week_chg
union all
select '6' as week, 8750 as revenue_ly, 0.972222 as week_chg
union all
select '7' as week, 9500 as revenue_ly, 1.085714 as week_chg
union all
select '8' as week, 10000 as revenue_ly, 1.052632 as week_chg
)ly
on ly.week > m.week
join (
select '4' as week, 8000 as revenue_ly, null as week_chg
union all
select '5' as week, 9000 as revenue_ly, 1.125 as week_chg
union all
select '6' as week, 8750 as revenue_ly, 0.972222 as week_chg
union all
select '7' as week, 9500 as revenue_ly, 1.085714 as week_chg
union all
select '8' as week, 10000 as revenue_ly, 1.052632 as week_chg
)ly2
on ly.week >= ly2.week
where 1 = 1
group by ly.week
get the latest record from main table:
select week, revenue
from (
select m.*,
row_number() over(partition by 1 order by week desc) rn
from (
select '1' as week, 10000 as revenue
union all
select '2' as week, 12000 as revenue
union all
select '3' as week, 15000 as revenue
union all
select '4' as week, 13500 as revenue
)m
)dt
where 1 = 1
and rn = 1