Transpose pivot table with date dimension in weeks as columns

Comments

4 comments

  • Avatar
    Tri Anthony Situmorang

    Hi Lisa,

     

    You can add a column in the Date Dimension table that stores the week number of each date using the WEEKOFYEAR() function.

    In the pivot table, use Year as Row and the new Week column as Column, you should be able to get the layout that you want.

    0
    Comment actions Permalink
  • Avatar
    Lisa Jay

    Hi Tri

    Thanks very much for your comment.

     

    My date dimension is a custom sql table, my SQL skills are not the best, am I able to insert another column into the SQL I am using for the Date Dimension?  This is the current SQL:

     

    Select Distinct x.[Common Date] from (

    Select [ORDER_DATE] AS [Common Date]
    FROM [FACT_ORDERS]

    Union All

    Select [REPORTEDREGDATE] AS [Common Date]
    FROM [FACT_REGISTRATIONS]


    ) as x

     

     

     

    Thanks

    Lisa

    0
    Comment actions Permalink
  • Avatar
    Tri Anthony Situmorang

    Hi Lisa,

    Yes you can add a column in custom SQL table as well. Please use the SQL below for your date dimension table (changes in bold font). Build the changes and then use the Week Number column as your Column in the pivot table.

     

    Select Distinct x.[Common Date], WEEKOFYEAR(x.[Common Date]) as [Week Number] from (

    Select [ORDER_DATE] AS [Common Date] 
    FROM [FACT_ORDERS]

    Union All

    Select [REPORTEDREGDATE] AS [Common Date] 
    FROM [FACT_REGISTRATIONS]


    ) as x

    0
    Comment actions Permalink
  • Avatar
    Lisa Jay

    Thank you so very much indeed, has worked a treat :-)

    0
    Comment actions Permalink

Please sign in to leave a comment.