28 Replies Latest reply: Aug 7, 2015 5:03 PM by Vinay kumar Bangari RSS

    Want to create Week Bucket Dimension in the script

    Vinay kumar Bangari

      Hi Experts,

       

      My requirement is little strange but you need your help. I have snapshot date column, YearWeek column, Now i need to create a column which is the combination of two weeks. For example if my first week in 2014 starts from aug-17-2014 (sunday ) to aug-24-2015 (sunday) and the week number is 201434. Now i wanted  a new column which will include two weeks start date is aug-17-2014 to aug-31-2014 and the value in the column should look like this 201434&201435... see the attached QVW with all the dates...and attached excel sheet with some sample data how the output should look like..

        • Re: Want to create Week Bucket Dimension in the script
          Sunny Talwar

          Not sure if I understand Vinay, but can't you just do this:

           

          If(Even(Week), YearWeek & YearWeek + 1, YearWeek - 1 & YearWeek) as NewColumn

            • Re: Want to create Week Bucket Dimension in the script
              Vinay kumar Bangari

              Sunny That does not work , what i wanted to is i want to group two weeks at a time as new dimension..

              • Re: Want to create Week Bucket Dimension in the script
                Sunny Talwar

                or this if you need & between them:

                 

                If(Even(Week), YearWeek & '&' & YearWeek + 1, YearWeek - 1 & '&' & YearWeek) as NewColumn

                  • Re: Want to create Week Bucket Dimension in the script
                    Vinay kumar Bangari

                    remember for one week means you will include 7 days, 1 month means 30 or 31 days.. now i want to create a new dimension with two week numbers

                        • Re: Want to create Week Bucket Dimension in the script
                          Sunny Talwar

                          Vinay isn't this what you want???

                           

                          Capture.PNG

                           

                          Script:

                           

                          Table:

                          LOAD DayOfYear,

                               SnapShotDate,

                               Week,

                               Year,

                               YearWeek,

                               If(Even(Week) = -1, YearWeek & '&' & (YearWeek + 1), YearWeek - 1 & '&' & YearWeek) as NewColumn

                          FROM

                          [week bucket output.xls]

                          (biff, embedded labels, table is Sheet1$);

                            • Re: Want to create Week Bucket Dimension in the script
                              Vinay kumar Bangari

                              Wow amazing ...In the similar manner can we create 3 more columns with 3 week , 4 week and 5 week buckets respectively..

                                • Re: Want to create Week Bucket Dimension in the script
                                  Sunny Talwar

                                  I think it can be done. Just need to know how they will be grouped?

                                   

                                  my current logic for 2 weeks was if week is even week then week & week + 1, but if its odd week then week-1 and week.

                                   

                                  What are the logic for other combinations?

                                    • Re: Want to create Week Bucket Dimension in the script
                                      Vinay kumar Bangari

                                      Please see the output how it should look like

                                      • Re: Want to create Week Bucket Dimension in the script
                                        Sunny Talwar

                                        Try this:

                                         

                                        Table:

                                        LOAD DayOfYear,

                                            SnapShotDate,

                                            Week,

                                            Year,

                                            YearWeek,

                                            If(Even(Week) = -1, YearWeek & '&' & (YearWeek + 1), YearWeek - 1 & '&' & YearWeek) as [2Weeks],

                                            If(Mod(AutoNumber(Week), 3) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

                                            If(Mod(AutoNumber(Week), 3) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

                                              (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek)) as [3Weeks],

                                            If(Mod(AutoNumber(Week), 4) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

                                            If(Mod(AutoNumber(Week), 4) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

                                            If(Mod(AutoNumber(Week), 4) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

                                              (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek))) as [4Weeks]

                                        FROM

                                        [week bucket output.xls]

                                        (biff, embedded labels, table is Sheet1$);

                                         

                                        The code will need some modifications based on your need.

                                         

                                        HTH

                                         

                                        Best,

                                        Sunny

                                          • Re: Want to create Week Bucket Dimension in the script
                                            Vinay kumar Bangari

                                            For the 3rd week and 4th week bucket the week should should start from week 201434 ...not from 201431... like dynamically it should take the minimum week as first week..

                                              • Re: Want to create Week Bucket Dimension in the script
                                                Sunny Talwar

                                                From your sample I am seeing 201434 as the minimum week:

                                                 

                                                Capture.PNG

                                                 

                                                Seems like this is what you want? Let me know if I am missing something....

                                                  • Re: Want to create Week Bucket Dimension in the script
                                                    Vinay kumar Bangari

                                                    pLEASE check this QVW file am i missing anything

                                                      • Re: Want to create Week Bucket Dimension in the script
                                                        Sunny Talwar

                                                        I don't have a licensed version of QlikView, so can't really open the file you are sharing. Can you share the script, if you have made any changes to it and a screenshot of the table box object?

                                                         

                                                        One thing to note here is that, you will need your data to be sorted in a correct order. So if it isn't sorted in Ascending order by SnapShotDate, I would recommend doing the 2Weeks, 3Weeks and 4Weeks calculation in preceding load or resident load with the order by statement.

                                                          • Re: Want to create Week Bucket Dimension in the script
                                                            Vinay kumar Bangari

                                                            Attached is the excel sheet and scripy

                                                             

                                                            Test:

                                                             

                                                             

                                                            LOAD DayOfYear,

                                                                SnapShotDate,

                                                                Week,

                                                                Year,

                                                                YearWeek,

                                                             

                                                             

                                                                  If(Even(Week) = -1, YearWeek & '-' & (YearWeek + 1), YearWeek - 1 & '-' & YearWeek) as [2Weeks],

                                                                If(Mod(AutoNumber(Week), 3) = 1, YearWeek & '-' & (YearWeek + 1) & '-' & (YearWeek + 2),

                                                                If(Mod(AutoNumber(Week), 3) = 2, (YearWeek - 1) & '-' & YearWeek & '-' & (YearWeek + 1),

                                                                  (YearWeek - 2) & '-' & (YearWeek - 1) & '-' & YearWeek)) as [3Weeks],

                                                                If(Mod(AutoNumber(Week), 4) = 1, YearWeek & '-' & (YearWeek + 1) & '-' & (YearWeek + 2) & '-' & (YearWeek + 3),

                                                                If(Mod(AutoNumber(Week), 4) = 2, (YearWeek - 1) & '-' & YearWeek & '-' & (YearWeek + 1) & '-' & (YearWeek + 2),

                                                                If(Mod(AutoNumber(Week), 4) = 3, (YearWeek - 2) & '-' & (YearWeek - 1) & '-' & YearWeek & '-' & (YearWeek + 1),

                                                                  (YearWeek - 3) & '-' & (YearWeek - 2) & '-' & (YearWeek - 1) & '-' & YearWeek))) as [4Weeks]

                                                            FROM

                                                            [C:\Users\kumarv\Desktop\Calendar Week.xls]

                                                            (biff, embedded labels, table is Sheet1$);

                                                              • Re: Want to create Week Bucket Dimension in the script
                                                                Sunny Talwar

                                                                See if this is what you want:

                                                                 

                                                                Table:

                                                                LOAD DayOfYear,

                                                                    SnapShotDate,

                                                                    Week,

                                                                    Year,

                                                                    YearWeek

                                                                FROM

                                                                [Calendar Week.xls]

                                                                (biff, embedded labels, table is Sheet1$);

                                                                 

                                                                [Final Table]:

                                                                LOAD *,

                                                                  If(Even(Week) = -1, YearWeek & '&' & (YearWeek + 1), YearWeek - 1 & '&' & YearWeek) as [2Weeks],

                                                                    If(Mod(AutoNumber(Week), 3) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

                                                                    If(Mod(AutoNumber(Week), 3) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

                                                                      (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek)) as [3Weeks],

                                                                    If(Mod(AutoNumber(Week), 4) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

                                                                    If(Mod(AutoNumber(Week), 4) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

                                                                    If(Mod(AutoNumber(Week), 4) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

                                                                      (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek))) as [4Weeks]

                                                                Resident Table

                                                                Order By SnapShotDate;

                                                                 

                                                                DROP Table Table;

                                                              • Re: Want to create Week Bucket Dimension in the script
                                                                Vinay kumar Bangari

                                                                yup got it brother , thanks a lot .

                                                                  • Re: Want to create Week Bucket Dimension in the script
                                                                    Sunny Talwar

                                                                    Awesome

                                                                     

                                                                    I am glad it worked out.

                                                                     

                                                                    Best,

                                                                    Sunny

                                                                      • Re: Want to create Week Bucket Dimension in the script
                                                                        Vinay kumar Bangari

                                                                        Yaar one last thing yaar.. sorry to disturb your sleep... can i have script for 5, 6,7 8, 9 and 10 weeks bucket.. i know i am asking too much but..

                                                                          • Re: Want to create Week Bucket Dimension in the script
                                                                            Sunny Talwar

                                                                            My daughter is keeping me up and not you.. so you don't have anything to worry about .

                                                                             

                                                                            I am working on it

                                                                            • Re: Want to create Week Bucket Dimension in the script
                                                                              Sunny Talwar

                                                                              Try this:

                                                                               

                                                                              Table:

                                                                              LOAD DayOfYear,

                                                                                  SnapShotDate,

                                                                                  Week,

                                                                                  Year,

                                                                                  YearWeek

                                                                              FROM

                                                                              [Calendar Week.xls]

                                                                              (biff, embedded labels, table is Sheet1$);

                                                                               

                                                                              [Final Table]:

                                                                              LOAD *,

                                                                                If(Even(Week) = -1, YearWeek & '&' & (YearWeek + 1), YearWeek - 1 & '&' & YearWeek) as [2Weeks],

                                                                                  If(Mod(AutoNumber(Week), 3) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

                                                                                  If(Mod(AutoNumber(Week), 3) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

                                                                                    (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek)) as [3Weeks],

                                                                                  If(Mod(AutoNumber(Week), 4) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

                                                                                  If(Mod(AutoNumber(Week), 4) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

                                                                                  If(Mod(AutoNumber(Week), 4) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

                                                                                    (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek))) as [4Weeks],

                                                                                  If(Mod(AutoNumber(Week), 5) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4),

                                                                                  If(Mod(AutoNumber(Week), 5) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

                                                                                  If(Mod(AutoNumber(Week), 5) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

                                                                                  If(Mod(AutoNumber(Week), 5) = 4, (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

                                                                                    (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & (YearWeek))))) as [5Weeks],

                                                                                  If(Mod(AutoNumber(Week), 6) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5),

                                                                                  If(Mod(AutoNumber(Week), 6) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4),

                                                                                  If(Mod(AutoNumber(Week), 6) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

                                                                                  If(Mod(AutoNumber(Week), 6) = 4, (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

                                                                                  If(Mod(AutoNumber(Week), 6) = 5, (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

                                                                                    (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & (YearWeek)))))) as [6Weeks],

                                                                                  If(Mod(AutoNumber(Week), 7) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6),

                                                                                  If(Mod(AutoNumber(Week), 7) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5),

                                                                                  If(Mod(AutoNumber(Week), 7) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4),

                                                                                  If(Mod(AutoNumber(Week), 7) = 4, (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

                                                                                  If(Mod(AutoNumber(Week), 7) = 5, (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

                                                                                  If(Mod(AutoNumber(Week), 7) = 6, (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

                                                                                    (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & (YearWeek))))))) as [7Weeks],

                                                                                  If(Mod(AutoNumber(Week), 8) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6) & '&' & (YearWeek + 7),

                                                                                  If(Mod(AutoNumber(Week), 8) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6),

                                                                                  If(Mod(AutoNumber(Week), 8) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5),

                                                                                  If(Mod(AutoNumber(Week), 8) = 4, (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4),

                                                                                  If(Mod(AutoNumber(Week), 8) = 5, (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

                                                                                  If(Mod(AutoNumber(Week), 8) = 6, (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

                                                                                  If(Mod(AutoNumber(Week), 8) = 7, (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

                                                                                    (YearWeek - 7) & '&' & (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & (YearWeek)))))))) as [8Weeks],

                                                                                  If(Mod(AutoNumber(Week), 9) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6) & '&' & (YearWeek + 7) & '&' & (YearWeek + 8),

                                                                                  If(Mod(AutoNumber(Week), 9) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6) & '&' & (YearWeek + 7),

                                                                                  If(Mod(AutoNumber(Week), 9) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6),

                                                                                  If(Mod(AutoNumber(Week), 9) = 4, (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5),

                                                                                  If(Mod(AutoNumber(Week), 9) = 5, (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4),

                                                                                  If(Mod(AutoNumber(Week), 9) = 6, (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

                                                                                  If(Mod(AutoNumber(Week), 9) = 7, (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

                                                                                  If(Mod(AutoNumber(Week), 9) = 8, (YearWeek - 7) & '&' & (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

                                                                                    (YearWeek - 8) & '&' & (YearWeek - 7) & '&' & (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & (YearWeek))))))))) as [9Weeks],

                                                                                  If(Mod(AutoNumber(Week), 10) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6) & '&' & (YearWeek + 7) & '&' & (YearWeek + 8) & '&' & (YearWeek + 9),

                                                                                  If(Mod(AutoNumber(Week), 10) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6) & '&' & (YearWeek + 7) & '&' & (YearWeek + 8),

                                                                                  If(Mod(AutoNumber(Week), 10) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6) & '&' & (YearWeek + 7),

                                                                                  If(Mod(AutoNumber(Week), 10) = 4, (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6),

                                                                                  If(Mod(AutoNumber(Week), 10) = 5, (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5),

                                                                                  If(Mod(AutoNumber(Week), 10) = 6, (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4),

                                                                                  If(Mod(AutoNumber(Week), 10) = 7, (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

                                                                                  If(Mod(AutoNumber(Week), 10) = 8, (YearWeek - 7) & '&' & (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

                                                                                  If(Mod(AutoNumber(Week), 10) = 9, (YearWeek - 8) & '&' & (YearWeek - 7) & '&' & (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

                                                                                      (YearWeek - 9) & '&' & (YearWeek - 8) & '&' & (YearWeek - 7) & '&' & (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & (YearWeek)))))))))) as [10Weeks]

                                                                              Resident Table

                                                                              Order By SnapShotDate;

                                                                               

                                                                              DROP Table Table;

                                                • Re: Want to create Week Bucket Dimension in the script
                                                  Antonio Mancini

                                                  Hi Vinay,

                                                  another way without changing script.

                                                  You can select YearWeek and select Num of columns by slider.

                                                  If You want to see all weeks then remove set analysis from expression

                                                  Regards,

                                                  Antonio