Calculating Week numbers

[dmxmikey]

I would like to define Week number based on invoice date, dependant on where the invoice date falls on the attached csv eg invoice date 28/12/2015 to be week 1

[sunny_talwar]

You should be able to use Week() function of QlikView. Have you tried that?

[dmxmikey]

Thanks for the response, unfortunately Im new to qlikview and not sure how to.

Thanks

[swuehl]

Try something like

Set DateFormat ='DD/MM/YYYY';

LOAD

     [Start date],

     [End Date],

     Week([Start date]) as WeekNo

FROM ...;

[swuehl]

Ah, you don't want ISO week numbering, so Week() function won't work.

See

Redefining the Week Numbers

maybe using

SET vCal_RD = 1;

[settu_periasamy]

Hi,

Do you want your Invoicedate falls within your Start date and End date? May be IntervalMatch() will helps.

Directory;
T1:
LOAD * INLINE [
    InvoiceDate
    12/28/2015
    2/29/2016
    3/21/2016
];
T2:
LOAD [Start date], 
     [End Date], 
     [Week No]
FROM
[Week Nos.xlsx]
(ooxml, embedded labels, table is [Week Nos]);
IntervalMatch(InvoiceDate) LOAD Distinct [Start date],[End Date] Resident T2;

[dmxmikey]

Set DateFormat ='DD/MM/YYYY';

LOAD

     [Start date],

     [End Date],

     Week([Start date]) as WeekNo

FROM ...;

This loads ok, but how will dates be determined?

[swuehl]

Are you looking for Weekstart() and Weekend() functions?

Like

LOAD Weekstart( [Invoice Date] ) as Weekstart,

          Date(Floor( Weekend([Invoice Date]))) as Weekend,

          Week( [Invoice Date] ) as WeekNo

FROM ...;

[dmxmikey]

what I would like to do is be able to report my sales figures using week numbers as the csv.

Hope it makes sense.

[swuehl]

If you have a fact table with sales figure and sales date, you can use a master calendar to create calendar fields from that:

The Master Calendar

If you need to redefine your week numbering from ISO to another numbering method, have a look at the code from Henric I posted in my previous answer.