Year to date with Year and Week

  • 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

  • spin (9/5/2016)


    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

    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

  • Quick thought, either do year-week or year-month, the two are incompatible

    😎

  • 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?

  • spin (9/5/2016)


    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?

    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

  • 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

  • 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??

  • 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

  • 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

  • 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

  • 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

  • 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