Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Want to create Week Bucket Dimension in the script

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..

1 Solution

Accepted Solutions
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

View solution in original post

28 Replies
Anonymous
Not applicable
Author

MRKachhiaIMP

@Massimo Grossi

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

Anonymous
Not applicable
Author

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

sunny_talwar

or this if you need & between them:

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

any help

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$);

Anonymous
Not applicable
Author

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

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?