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: 
lakshmanvepuri
Creator
Creator

Generating the Custom Date based on the Work Week of the calendar

Hi ,

 

I will receive the file alternate week and based on that i need to generate the below two columns.

Suppose I receive File on 10-June-2020... I should Display 

vVersion ---  WW24_2020

vDate -------  18-JUNE-2020 

Please check the attached file for the calendar.

 

Thanks.

Vepuri

1 Solution

Accepted Solutions
vamsee
Specialist
Specialist

Hi,

I have defaulted the year to be 2020.

Try

Column1:

'WW'&Week(MakeDate('2020', Month(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD')), Day(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD'))))&'_' &

Year(MakeDate('2020', Month(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD')), Day(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD'))))

 

Column2:

Date(WeekEnd(MakeDate('2020', Month(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD')), Day(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD'))))+5, 'DD-MMMM-YYYY')

 

 For Column 1 you can also use the Filename like SubField(Filebasename(), '_',2)

View solution in original post

3 Replies
vamsee
Specialist
Specialist

Assuming your file name would have a date, in it. (You can use Filebasename)

Try

'WW'&Week(FileBasename())&'_' &Year(FileBasename()) as Column1,
Date(WeekEnd(FileBasename())+5, 'DD-MMMM-YYYY') as Column2,

If you want only the Thursday's highlighted in Yellow to be used in Column2, you might've to get a list of dates for each year.

I don't think an excel in that format would be useful.



lakshmanvepuri
Creator
Creator
Author

HI @vamsee 

 

The File Name will be like this..

Eg:

M05_WW22_DC_May17_MasterData_Up.xlsx

M06_WW24_DC_Jun17_MasterData_Up.xlsx

M06_WW26_DC_Jun17_MasterData_Up.xlsx

vamsee
Specialist
Specialist

Hi,

I have defaulted the year to be 2020.

Try

Column1:

'WW'&Week(MakeDate('2020', Month(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD')), Day(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD'))))&'_' &

Year(MakeDate('2020', Month(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD')), Day(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD'))))

 

Column2:

Date(WeekEnd(MakeDate('2020', Month(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD')), Day(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD'))))+5, 'DD-MMMM-YYYY')

 

 For Column 1 you can also use the Filename like SubField(Filebasename(), '_',2)