September 5, 2016 at 1:31 pm
Hi
I have a bit of a conundrum
I have a table which i've aggregated to :
Year, Week, Customer, Product, Quantity CY, Value CY, Quantity PY, Value PY
CY = current year, PY = previous year
I'm trying to create a ytd value for both CY and PY but am not getting the expected result.
i'm using...
,SUM([CY_Quantity]) OVER (PARTITION BY [Customer], [Product], [Year] ORDER BY [Customer], [Product], [Year], [WeekOfYear]) as [CY_Quantity_YTD]
can anyone help with this, it's driving me mental!!
Thanks
September 5, 2016 at 1:39 pm
spin (9/5/2016)
HiI have a bit of a conundrum
I have a table which i've aggregated to :
Year, Week, Customer, Product, Quantity CY, Value CY, Quantity PY, Value PY
CY = current year, PY = previous year
I'm trying to create a ytd value for both CY and PY but am not getting the expected result.
i'm using...
,SUM([CY_Quantity]) OVER (PARTITION BY [Customer], [Product], [Year] ORDER BY [Customer], [Product], [Year], [WeekOfYear]) as [CY_Quantity_YTD]
can anyone help with this, it's driving me mental!!
Thanks
can you not amend your aggregation code (
I have a table which i've aggregated to :
Year, Week, Customer, Product, Quantity CY, Value CY, Quantity PY, Value PY
) to exclude week?
what code have you used for your aggregations?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 5, 2016 at 1:51 pm
Quick thought, either do year-week or year-month, the two are incompatible
😎
September 5, 2016 at 3:30 pm
what i'm trying to do is a year on year comparison
so the first thing i did was to create 2 data sets, one for current year - week and and one for previous year - week. I added a year to the previous year - week data set and joined it to current year.
Then i added the last day of the week for the current year and ended up with
Year Week customer product CY PY LastWeekDay
2016 1 c123 sku345 400 370 26/08/2016
2016 1 c123 sku345 500 550 02/09/2016
Is it possible to ytd these?
September 5, 2016 at 3:44 pm
spin (9/5/2016)
what i'm trying to do is a year on year comparisonso the first thing i did was to create 2 data sets, one for current year - week and and one for previous year - week. I added a year to the previous year - week data set and joined it to current year.
Then i added the last day of the week for the current year and ended up with
Year Week customer product CY PY LastWeekDay
2016 1 c123 sku345 400 370 26/08/2016
2016 1 c123 sku345 500 550 02/09/2016
Is it possible to ytd these?
can you please define "current Year" is it jan 1st 2016 to current (specified?) date....and therefore "previous year" is jan 1st 2015 to current date (minus 1 year) ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 5, 2016 at 5:40 pm
Just a guess here but have you tried adding this to your OVER clause?
SUM([CY_Quantity]) OVER (PARTITION BY [Customer], [Product], [Year]
ORDER BY [Customer], [Product], [Year], [WeekOfYear]
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 6, 2016 at 4:22 am
Hi, thanks for the replies
I have attached some script which creates a couple of simple tables/data and then create the data set i have.
I'm still not getting the correct result when i add ytd. when i pivot the data i would expect to be able to see a year to date value by week, i could then add customer and view by ytd and the same for product. Is what i'm trying actually possible?
Thanks
-- create the sales table
create table SalesTransactions
(
TrnDate DateTime,
Customervarchar(5),
Product varchar(5),
Sales int
)
-- add some sales
INSERT INTO SalesTransactions (TrnDate, Customer, Product, Sales) VALUES ('2016-01-04', 'C1234', 'P1234', 1740)
INSERT INTO SalesTransactions (TrnDate, Customer, Product, Sales) VALUES ('2016-01-05', 'C2345', 'P1234', 1994)
INSERT INTO SalesTransactions (TrnDate, Customer, Product, Sales) VALUES ('2016-01-13', 'C1234', 'P1234', 1138)
INSERT INTO SalesTransactions (TrnDate, Customer, Product, Sales) VALUES ('2016-01-05', 'C1234', 'P2345', 1901)
INSERT INTO SalesTransactions (TrnDate, Customer, Product, Sales) VALUES ('2016-01-20', 'C2345', 'P1234', 1537)
INSERT INTO SalesTransactions (TrnDate, Customer, Product, Sales) VALUES ('2016-02-03', 'C1234', 'P1234', 1424)
INSERT INTO SalesTransactions (TrnDate, Customer, Product, Sales) VALUES ('2016-02-03', 'C2345', 'P3456', 1888)
INSERT INTO SalesTransactions (TrnDate, Customer, Product, Sales) VALUES ('2015-01-06', 'C1234', 'P1234', 1967)
INSERT INTO SalesTransactions (TrnDate, Customer, Product, Sales) VALUES ('2015-01-10', 'C2345', 'P1234', 1240)
INSERT INTO SalesTransactions (TrnDate, Customer, Product, Sales) VALUES ('2015-01-15', 'C1234', 'P2345', 1323)
INSERT INTO SalesTransactions (TrnDate, Customer, Product, Sales) VALUES ('2015-01-22', 'C2345', 'P1234', 1598)
INSERT INTO SalesTransactions (TrnDate, Customer, Product, Sales) VALUES ('2015-01-27', 'C2345', 'P1234', 1859)
INSERT INTO SalesTransactions (TrnDate, Customer, Product, Sales) VALUES ('2015-02-04', 'C1234', 'P1234', 1247)
INSERT INTO SalesTransactions (TrnDate, Customer, Product, Sales) VALUES ('2015-02-06', 'C2345', 'P3456', 1079)
-- create the calendar
-- added the first 5 weeks of 2015, 2016
create table Calendar
(
Date datetime,
Year int,
WeekOfYear int
)
-- add some dates
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-01', 2015, 1)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-02', 2015, 1)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-03', 2015, 1)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-04', 2015, 2)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-05', 2015, 2)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-06', 2015, 2)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-07', 2015, 2)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-08', 2015, 2)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-09', 2015, 2)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-10', 2015, 2)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-11', 2015, 3)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-12', 2015, 3)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-13', 2015, 3)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-14', 2015, 3)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-15', 2015, 3)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-16', 2015, 3)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-17', 2015, 3)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-18', 2015, 4)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-19', 2015, 4)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-20', 2015, 4)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-21', 2015, 4)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-22', 2015, 4)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-23', 2015, 4)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-24', 2015, 4)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-25', 2015, 5)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-26', 2015, 5)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-27', 2015, 5)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-28', 2015, 5)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-29', 2015, 5)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-30', 2015, 5)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2015-01-31', 2015, 5)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-01', 2016, 1)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-02', 2016, 1)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-03', 2016, 2)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-04', 2016, 2)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-05', 2016, 2)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-06', 2016, 2)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-07', 2016, 2)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-08', 2016, 2)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-09', 2016, 2)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-10', 2016, 3)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-11', 2016, 3)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-12', 2016, 3)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-13', 2016, 3)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-14', 2016, 3)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-15', 2016, 3)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-16', 2016, 3)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-17', 2016, 4)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-18', 2016, 4)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-19', 2016, 4)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-20', 2016, 4)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-21', 2016, 4)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-22', 2016, 4)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-23', 2016, 4)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-24', 2016, 5)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-25', 2016, 5)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-26', 2016, 5)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-27', 2016, 5)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-28', 2016, 5)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-29', 2016, 5)
INSERT INTO Calendar (Date, Year, WeekOfYear) VALUES ('2016-01-30', 2016, 5)
-- create the data set with cy and py values
;with cte
as (
-- aggregate the sales transaction to year, week
select Year, WeekOfYear, Customer, Product, sum(Sales) Sales
from SalesTransactions a
join Calendar b on a.TrnDate = b.Date
group by Year, WeekOfYear,Customer, Product
)
select dts.Year,
dts.WeekOfYear,
cal.LastWeekDay,
dts.Customer,
dts.Product,
isnull(cy.Sales,0) SalesCY,
case
when cy.Sales is null then 0
else SUM(cy.Sales) OVER (PARTITION BY dts.Year ORDER BY dts.Customer, dts.Product, cal.LastWeekDay)
end as SalesCYTD_V1,
case
when cy.Sales is null then 0
else SUM(cy.Sales) OVER (PARTITION BY dts.Year, dts.Customer, dts.Product ORDER BY dts.Customer, dts.Product, cal.LastWeekDay)
end as SalesCYTD_V2,
SUM(cy.Sales) OVER (PARTITION BY dts.[Customer], dts.[Product], dts.[Year]
ORDER BY dts.[Customer], dts.[Product], dts.[Year], dts.[WeekOfYear]
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as SalesCYTD_V3,
py.Sales SalesPY
from (
-- create the current year data set which includes items sold last year but not this
select Year(getdate()) as Year, cte.WeekOfYear, cte.Customer, cte.Product
from cte
group by WeekOfYear, Customer, Product
) dts
-- add surrent year sales
left join (
select Year, WeekOfYear, Customer, Product, Sales from cte where Year = year(getdate())
) cy
on dts.Year = cy.Year and dts.WeekOfYear = cy.WeekOfYear and dts.Customer = cy.Customer and dts.Product = cy.Product
-- add previous year sales. map to this years weeks
left join (
select Year + 1 as Year, WeekOfYear, Customer, Product, Sales from cte where Year = year(getdate()) -1
) py
on dts.Year = py.Year and dts.WeekOfYear = py.WeekOfYear and dts.Customer = py.Customer and dts.Product = py.Product
-- add the last day of the week
left join (
select max(Date) LastWeekDay, Year, WeekOfYear from Calendar group by Year, WeekOfYear
) cal
on dts.Year = cal.Year and dts.WeekOfYear = cal.WeekOfYear
-- The big question is how do i now apply a ytd value??
September 6, 2016 at 6:24 am
Spin, would you expect the same results from this data as the results you displayed earlier?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 6, 2016 at 6:54 am
i think so? 😐
I really want to be able to pivot the data in excel so i can see ytd by
week
week - product
week - customer
week - customer - product
September 6, 2016 at 9:56 am
spin (9/6/2016)
i think so? 😐I really want to be able to pivot the data in excel so i can see ytd by
week
week - product
week - customer
week - customer - product
maybe I misunderstand.....but are you saying you are going to take the output from SQL into Excel and then perform various pivots?
if that is true then surely your intial cte provides sufficient data for Excel to pivot on and provide the additional YTD's?
eg
+------------------------------------------------------------------------------------------+
¦ ¦ Year ¦ ¦ ¦ ¦ ¦ ¦
¦-------------+--------------+------+--------------+------+--------------------+-----------¦
¦ ¦ 2015 ¦ ¦ 2016 ¦ ¦ Total Sum of Sales ¦ Total YTD ¦
¦-------------+--------------+------+--------------+------+--------------------+-----------¦
¦ Week ¦ Sum of Sales ¦ YTD ¦ Sum of Sales ¦ YTD ¦ ¦ ¦
¦-------------+--------------+------+--------------+------+--------------------+-----------¦
¦ 2 ¦ 3207 ¦ 3207 ¦ 5635 ¦ 5635 ¦ 8842 ¦ 8842 ¦
¦-------------+--------------+------+--------------+------+--------------------+-----------¦
¦ 3 ¦ 1323 ¦ 4530 ¦ 1138 ¦ 6773 ¦ 2461 ¦ 11303 ¦
¦-------------+--------------+------+--------------+------+--------------------+-----------¦
¦ 4 ¦ 1598 ¦ 6128 ¦ 1537 ¦ 8310 ¦ 3135 ¦ 14438 ¦
¦-------------+--------------+------+--------------+------+--------------------+-----------¦
¦ 5 ¦ 1859 ¦ 7987 ¦ ¦ 8310 ¦ 1859 ¦ 16297 ¦
¦-------------+--------------+------+--------------+------+--------------------+-----------¦
¦ Grand Total ¦ 7987 ¦ ¦ 8310 ¦ ¦ 16297 ¦ ¦
+------------------------------------------------------------------------------------------+
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 6, 2016 at 12:56 pm
The Dixie Flatline (9/5/2016)
Just a guess here but have you tried adding this to your OVER clause?
SUM([CY_Quantity]) OVER (PARTITION BY [Customer], [Product], [Year]
ORDER BY [Customer], [Product], [Year], [WeekOfYear]
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Also, have you tried REMOVING this from your query?
SUM([CY_Quantity]) OVER (PARTITION BY [Customer], [Product], [Year]
ORDER BY [Customer], [Product], [Year], [WeekOfYear]
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Sorting by a partition expression is spurious. By definition, all of the records within a partition have the same value for each of the partition expressions, so any possible ordering on a partition expression would be valid. Including the partition expressions in your order by only serves to clutter your code.
Another way to think about this, is that the order by already includes the partition expressions, because it orders by the partition expressions in order to determine the segmentation.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 6, 2016 at 1:32 pm
You are right, Drew. I missed that in my cutting and pasting. Thank you for setting it straight. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply