Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
viveksingh
Creator III
Creator III

Custom weeks

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
1 Solution

Accepted Solutions
PrashantSangle

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;

 

 

 

 

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

14 Replies
swapnilk
Partner - Contributor
Partner - Contributor

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

PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
viveksingh
Creator III
Creator III
Author

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
Anonymous
Not applicable

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.

 

 

viveksingh
Creator III
Creator III
Author

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
PrashantSangle

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;

 

 

 

 

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
viveksingh
Creator III
Creator III
Author

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
viveksingh
Creator III
Creator III
Author

Attached is the weekly report I’m expecting
PrashantSangle

Have you tried that script

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

 

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂