topic Re: Custom weeks in Water Cooler

Custom weeks

viveksingh — Wed, 14 Nov 2018 05:50:42 GMT

Hi experts,
I have data like below
Week_st Week_end week
01/10/2018 05/10/2018 Week1
06/10/2018 12/10/2018 Week2
13/10/2018 25/10/2018 Week3

I want to creat week nunbers like week1,2,3,... based on above data

Expecting like below
Week_st Week_end week
01/10/2018 05/10/2018 Week1
06/10/2018 12/10/2018 Week2
13/10/2018 25/10/2018 Week3
01/10/2017 05/10/2017 Week1
06/10/2017 12/10/2017 Week2
13/10/2017 25/10/2017 Week3

Please help

Re: Custom weeks

swapnilk — Wed, 14 Nov 2018 06:11:50 GMT

Create a variable vWeekNumber,

vWeekNumber = if(Date(Week_st ) = Date(01/10/2018) and Date(Week_end ) = Date (05/10/2018) , Week1, if(Date(Week_st ) = Date(06/10/2018) and Date(Week_end ) = Date (12/10/2018) , Week2, if(Date(Week_st ) = Date(13/10/2018) and Date(Week_end ) = Date (25/10/2018) , Week3)))

Re: Custom weeks

PrashantSangle — Wed, 14 Nov 2018 06:19:06 GMT

If you already have week in source data then why you want to repeat it??

Sorry but I am not getting your question. Your source data and expected output look same except it's repeatation.

Regards,

Prashant Sangle

Re: Custom weeks

viveksingh — Wed, 14 Nov 2018 06:40:59 GMT

Thanks for the reply. This week_st and week_end are alvailable for 2018. I have to populate week numbers for 2017 also. So to get 2017 dates i have used addmonths() function. Now in my week_st and week_end have 2018 and 2017. But I want add week numbers to 2017 also

Re: Custom weeks

Anonymous — Wed, 14 Nov 2018 06:42:13 GMT

Hello Vivek,

I don't see a difference in your data and the requirement. Please rectify.

I assume you have two dates column (Week_st, Week_end) and you need month wise week number as your output.

Use the following code:

T1:
load * Inline
[
Week_st,Week_end
01/10/2018,07/10/2018
08/10/2018,14/10/2018
15/10/2018,21/10/2018
22/10/2018,28/10/2018
];

T2:
load Week_st, Week_end,
'Week'&
if(week(date#(Week_st,'DD/MM/YYYY'))-week(monthstart(date#(Week_st,'DD/MM/YYYY')))+1>1,
week(date#(Week_st,'DD/MM/YYYY'))-week(monthstart(date#(Week_st,'DD/MM/YYYY')))+1,
'1')
as week
Resident T1;

drop Table T1;

Regards,

Amardeep.

Re: Custom weeks

viveksingh — Wed, 14 Nov 2018 06:44:37 GMT

Thanks for the reply. Yes, my source have week numbers but it is onky for 2018. I need for 2017 also.
In my report I have to show report on weekly basis. If we pivot, under week1, i’m ge Only 2018. But i need 2018 and 2017 data together under weeks

Re: Custom weeks

PrashantSangle — Wed, 14 Nov 2018 06:48:32 GMT

Is date for year 2017 already present in your data? do you have any other catch than this??

Can you share more sample data. try below

Load week_st,

week_end,

week

from tableName;

concatenate

Load AddYears(week_st,-1) as week_st,

AddYears(week_end,-1) as week_end,

week

from tableName;

Re: Custom weeks

viveksingh — Wed, 14 Nov 2018 06:56:44 GMT

I have main data for all years.
But calandar will be defined only for current year like week_st and week_end. I can get 2017 calndar using addmonths() function for the same dates period for another’s year. You just used addyears() and I used addmonths() thats fine. Now how can I add week numbers to 2017

Re: Custom weeks

viveksingh — Wed, 14 Nov 2018 06:59:48 GMT

Attached is the weekly report I’m expecting

Re: Custom weeks

PrashantSangle — Wed, 14 Nov 2018 07:03:49 GMT

Have you tried that script

It will work. Week is already get added as per current year week.

Re: Custom weeks

qlikviewwizard — Wed, 14 Nov 2018 07:25:36 GMT

Hi,

Please try this.

Data:
LOAD DATE(Date#(Week_st,'DD/MM/YYYY')) as Week_st, DATE(Date#(Week_end,'DD/MM/YYYY')) as Week_end, week INLINE [
Week_st, Week_end, week
01/10/2018, 05/10/2018, Week1
06/10/2018, 12/10/2018, Week2
13/10/2018, 25/10/2018, Week3
];
Join(Data)
LOAD addyears (Week_st,-1) as Week_st,addyears (Week_end,-1) as Week_end,week Resident Data;

Re: Custom weeks

qlikviewwizard — Wed, 14 Nov 2018 07:27:51 GMT

Hi Please try this.

Re: Custom weeks

viveksingh — Wed, 14 Nov 2018 07:40:52 GMT

Thanks for the reply. It is working fine.
Here I have date when I add week to pivot I’m not getting 2017 data.

Re: Custom weeks

qlikviewwizard — Wed, 14 Nov 2018 08:12:20 GMT

Please select Correct and Helpful to close the thread. Thank you.

Re: Custom weeks

qlikviewwizard — Thu, 15 Nov 2018 03:51:01 GMT

Hi,

Please try like this.

Data:
LOAD Year(DATE(Date#(Week_st,'DD/MM/YYYY')) ) as Year,DATE(Date#(Week_st,'DD/MM/YYYY')) as Week_st,
DATE(Date#(Week_end,'DD/MM/YYYY')) as Week_end, week
INLINE [
Week_st, Week_end, week
01/10/2018, 05/10/2018, Week1
06/10/2018, 12/10/2018, Week2
13/10/2018, 25/10/2018, Week3
];
Join(Data)
LOAD Year(addyears (Week_st,-1)) as Year,addyears (Week_st,-1) as Week_st,addyears (Week_end,-1) as Week_end,week
Resident Data;