Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Specialist
Specialist

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

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
Highlighted
Specialist
Specialist

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

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.



Highlighted
Creator
Creator

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

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

Highlighted
Specialist
Specialist

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

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