Creating back end table

[bushpalaamarnat]

Hello All,

Could you please, based on today's date need to calculate current week , current week -6 and current week + 13.

attaching the out put in the excel file. Also, this need's to be created in back end  and store automatically.

Any suggestion, please let me know.

[olivierrobin]

hello

you can use the week function   week(date)

so week(today()) for the current week

week(today()-6) for past 6

and week(today()+13) for 13 days in the future

[MK_QSL]

You can use below in script.

Week(Date) as [Week No]

[bushpalaamarnat]

Sir, is there a better way to write, Week(today()-6), Week(today()-5) and so on..

for each week number i need to create a formula.

Regards,

Neelima.

[olivierrobin]

apply the function to your date column

[bushpalaamarnat]

You can use below in script.

Week(Date) as [Week No]

This make sense, there is no date column we have to create date column based on today's date.

[MK_QSL]

Provide input data as sample file

output data as per your requirement

[petter]

This is the script I would recommend for you:

LOAD
  S.No,
  Date,
  [Week No],
  Week,
  [Week Representation],
  Dual('W' & If( RelWeek >= 0 , RelWeek+1 , RelWeek), RelWeek) AS [Week Labels];
LOAD 
     S.No, 
     Date, 
     Week(Date) AS [Week No], 
     'W'&Week(Date)&'-'&Right(Year(Date),2) AS Week, 
     If( Week(Date) = Week(Today(1)) , 'Current', If( Week(Date) < Week(Today(1)) , 'History' , 'Future')) AS [Week Representation],
     (Week(Date)-Week(Today(1))) AS RelWeek, 
     'W' & (Week(Date)-Week(Today(1))) AS [Week Labels]
FROM

(ooxml, embedded labels, table is Sheet1)
WHERE IsNum(S.No);  // Necessary for the Excel file you provided

I used the Dual function in QlikView to create a single field with both a text label and a numeric value as you can see in the last two columns: