topic Re: Calculating Week numbers in QlikView

Calculating Week numbers

dmxmikey — Fri, 22 Jan 2016 11:38:30 GMT

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

Re: Calculating Week numbers

sunny_talwar — Fri, 22 Jan 2016 11:52:01 GMT

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

Re: Calculating Week numbers

dmxmikey — Fri, 22 Jan 2016 11:53:11 GMT

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

Thanks

Re: Calculating Week numbers

swuehl — Fri, 22 Jan 2016 12:00:02 GMT

Try something like

Set DateFormat ='DD/MM/YYYY';

LOAD

[Start date],

[End Date],

Week([Start date]) as WeekNo

FROM ...;

Re: Calculating Week numbers

swuehl — Fri, 22 Jan 2016 12:09:34 GMT

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;

Re: Calculating Week numbers

settu_periasamy — Fri, 22 Jan 2016 12:22:32 GMT

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;

Re: Calculating Week numbers

dmxmikey — Fri, 22 Jan 2016 12:48:39 GMT

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?

Re: Calculating Week numbers

swuehl — Fri, 22 Jan 2016 14:48:42 GMT

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

LOAD Weekstart( [Invoice Date] ) as Weekstart,

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

Week( [Invoice Date] ) as WeekNo

FROM ...;

Re: Calculating Week numbers

dmxmikey — Mon, 25 Jan 2016 09:33:08 GMT

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

Hope it makes sense.

Re: Calculating Week numbers

swuehl — Mon, 25 Jan 2016 10:56:36 GMT

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.