Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You should be able to use Week() function of QlikView. Have you tried that?
Thanks for the response, unfortunately Im new to qlikview and not sure how to.
Thanks
Try something like
Set DateFormat ='DD/MM/YYYY';
LOAD
[Start date],
[End Date],
Week([Start date]) as WeekNo
FROM ...;
Ah, you don't want ISO week numbering, so Week() function won't work.
See
maybe using
SET vCal_RD = 1;
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;
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?
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 ...;
what I would like to do is be able to report my sales figures using week numbers as the csv.
Hope it makes sense.
If you have a fact table with sales figure and sales date, you can use a master calendar to create calendar fields from that:
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.