topic Re: Week Number in QlikView

Week Number

ciaran_mcgowan — Mon, 17 Oct 2016 09:40:39 GMT

Hi all,

Is there a way to alter the Week() Function with custom numbers?

I have an issue where the first week in the year differs from year to year (up to several weeks difference in some cases). It's for tracking marketing campaigns so some years they start in August, other years can be November (with non specific date starts). One year was 15/08/2015 and another was 03/11/2012.

Another issue is that these campaigns don't run every week but can in some cases so I need each week to only be incremented if there is data. Here's an example from my current data set from the beginning of 2012:

Campaign	Date	Week
Discount Email Blast	15/08/2012	33
Discount Email Blast	23/08/2012	34
Discount Email Blast	03/09/2012	36
Discount Email Blast	19/09/2012	38

What I need these to read is:

Campaign	Date	Week
Discount Email Blast	15/08/2012	1
Discount Email Blast	23/08/2012	2
Discount Email Blast	03/09/2012	3
Discount Email Blast	19/09/2012	4

Any ideas?

Re: Week Number

sunny_talwar — Mon, 17 Oct 2016 09:47:28 GMT

What is the logic behind different week numbers each year?

Re: Week Number

rubenmarin — Mon, 17 Oct 2016 09:57:17 GMT

Hi Ciarán, you'll need to adapt using each year start date but the idea can be a simple substract to generate the week number, like:

=Ceil((Date(Date_To_Generate_Week)-Date(Year_Start_Date))/7)

=Ceil((Date('23/08/2012')-Date('15/08/2012'))/7)

Yo can do a bucle wich loads yearstart and yearend (the start of the next year?) for each year, first thing the bucle does is creating the vStartYearDate variable to use in the script:

=Ceil((Date(DateField)-Date(vStartYearDate))/7)

Btw, if years starts in different dates there will be dates wich belongs to 2 years or there will be different years size (in example: years with 300 days and others with 400)

Re: Week Number

ciaran_mcgowan — Mon, 17 Oct 2016 10:02:28 GMT

Hi Sunny,

The logic is as follows:

Each file is called 2014-Campaigns, 2015-Campaigns, etc.

The first week starts on the week that contains the first values and that needs to be Week 1. If a week contains no values, then it shouldn't be counted in the week numbers. The next week that contains values will be the next week number.

Re: Week Number

ciaran_mcgowan — Mon, 17 Oct 2016 11:05:21 GMT

Hi Ruben,

Thanks, I'll give this a try. FYI these Campaigns only run for 8 months a year so there's no chance of any overlap.

Re: Week Number

rubenmarin — Mon, 17 Oct 2016 11:19:59 GMT

I just noted that maybe it needs a '+1':

=Ceil((Date(DateField)-Date(vStartYearDate)+1)/7)

Re: Week Number

ciaran_mcgowan — Mon, 17 Oct 2016 11:58:47 GMT

Hi Ruben,

Thanks for that. It works as far as renumbering the weeks goes, but it doesn't help with the broken week issue. I'm still going to get gaps in the week numbers.

Re: Week Number

rubenmarin — Mon, 17 Oct 2016 12:40:41 GMT

Hi Ciarán, sorry I overlooked that, maybe adding a calendar wich created all dates and the associated week number, like:

LET vStart = num(date('13/08/2012')); // This should be assigned in a bucle by campaign

LET vEnd = num(date('06/06/2013')); // This should be assigned in a bucle by campaign

Calendar:

LOAD Date,

Ceil((Date(Date)-Date($(vStart))+1)/7) as WeekNum;

LOAD Date($(vStart)+IterNo()-1) as Date

AutoGenerate 1

while $(vStart)+IterNo()<=$(vEnd);

Also note that with this approach the first week will have always 7 days, if you need match natural weeks it will need some fix comparing with the day of the week it starts.

Re: Week Number

ciaran_mcgowan — Mon, 17 Oct 2016 16:19:30 GMT

Thanks again for you suggestion Ruben. I've been trying a few variations of your approach but there are still gaps at different stages.

I'll keep trying and let you know how I get on.

Re: Week Number

rubenmarin — Wed, 19 Oct 2016 09:56:57 GMT

Hi Ciarán, or try to upload a sample so I can check the gaps, maybe I lose some datails because of my bad english

Re: Week Number

ciaran_mcgowan — Wed, 19 Oct 2016 10:57:14 GMT

Hi Ruben,

I've been trying a few different ways to accomplish what I want and I think I cracked it this morning. Instead of loading all files at once and trying to create a Week Number, I created a loop to only load one at a time.

temp1:
LOAD * FROM ;

temp2:
LOAD DISTINCT Campaign Resident temp1;

DROP TABLE temp1;

FOR i = 0 TO NoOfRows('temp2')
LET vCampaign = Peek('Campaign', $(i), temp2);

Campaign:
LOAD *,
  Campaign & '-' & Week(Date) AS CampaignWeekID
FROM ;

Within the Campaign table, I created a new field called CampaignWeekID. Then I loaded all distinct CampaignWeekID's into another temp table to find out how many distinct weeks there were in each Campaign year (numbers vary from year to year).

CampaignWeekCount:

LOAD DISTINCT CampaignWeekID AS WeekCount Resident Campaign;

Next I create a loop for each distinct week for that year and another temp table to store the results of each loop.

FOR j = 0 TO NoOfRows('CampaignWeekCount')-1
LET vCampaignWeekID = Peek('WeekCount', $(j), CampaignWeekCount);

Campaign_tmp:
LOAD WeekCount,
  '$(CampaignWeekID)' AS CampaignWeekID,
  ('$(j)'+1) AS WeekNum
Resident CampaignWeekCount;

NEXT j

DROP TABLE CampaignWeekCount;

Then I joined the distinct values back to the original table and dropped the temporary one and moved on to the next campaign year.

LEFT JOIN(Campaign)
LOAD DISTINCT
  CampaignWeekID,
  WeekNum
Resident Campaign_tmp;

DROP Table Campaign_tmp;

STORE Campaign INTO ;
DROP TABLE Campaign;

NEXT i

DROP TABLE temp2;

It might seem a little inefficient, but I wasn't having any luck with your approach or by using functions like RowNo() or AutoNumber(). I'm still testing my results but all looks good so far.