Discussion Board for collaboration on QlikView Scripting.
Is there any option in QlikView to set the week start globally to desired Day instead of default Monday.
I have tried using -1 in WeekStart but there are lot of problems.
Please check these out
LET vCYDate = Date(Today());
LET vCYWeekStart = WeekStart(vCYDate,0,-1);
LET vCYWeekNo = Week(vCYWeekStart);
LET vPYWeekDay = MakeWeekDate(Year(vCYDate)-1,vCYWeekNo, vCYWeekDay);
LET vPYWeekStart = WeekStart(vPYWeekDay,0,-1);
LET vPYWeekNo = Week(vPYWeekStart);
The main thing is I want to get the same week details for 2 given years. Try for Year 2012 and 2011 in the way I have written the code. Except first week of Jan, the result is giving the correct result. Is there a way to overcome this?
Try using WEEKNUM(<Date>) in Excel for both these dates (Jan 1st 2012 and Jan 1st 2011) as well in QlikView.
Actually the WeekStart shows Sunday but the Week number of that date is showing 1 week less than the correct week # as the vCYWeekStart / vPYWeekStart is considered as the last day of the previous week in QlikView.
How to globally set in QV to make Sunday as the start of the week and the rest of the date calculations follow it?
Solved! Go to Solution.
If you use the attached script, then you can calculate week number and week day so that Jan 1st always belongs to week 1 and Sunday always is the first day of the week. Then you can compare year-to-year numbers using WeekNumber and WeekDay: (Note that the difference from previous year is calculated also)
Should you want to calculate the date of the previous year's corresponding date in the script, you can do this also. See attachment.
at the start of edit script while defineing varibles
you can change the order of
SET TimeFormat='h:mm:ss TT';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
instead of SETDayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
and save application
hope this helps
is similar to SET DayNames='q;w;e;r;t;y;u';
It is just to customize the language and not the real start of the week
I tried this and it shows April 30th 2012 as Sunday though it is Monday
Atlast I got fedup with QV's inbuild date functions and wrote a VBScript to get the previous year's week date
ctYear = Datepart("yyyy",dDate)
ctDayofYear = Datepart("y",dDate)
cyFirstDay = Datepart("w",datevalue("1/1/" & ctYear))
pyFirstDay = Datepart("w",datevalue("1/1/" & ctYear - 1))
GetPYWeekDate = dateadd("d", - (pyFirstDay - cyFirstDay) + ctDayofYear - 1, datevalue("1/1/" & ctYear - 1))
If anyone needs this, use it
It's easier if you do it in the script instead of in VBS. Leave the DayNames variable as it is and use
Dual(Subfield('$(DayNames)',';',WeekDay(Date)+1),Mod(Date-1,7)) as MyWeekDay
Thanks for your input. It is returning 1 day more than the QV's normal weekday
Today() : 5/8/2012 (Tuesday)
WeekDay: Num(WeekDay(Today()),'#') : 1
MyWeekDay: Num(Dual(Subfield('$(DayNames)',';',WeekDay(Today())+1),Mod(Today()-1,7)),'#') : 2
If its Tuesday, it should return either 2(Weekday) / 3(MyWeekDay), but it is returning 1 / 2. Does this mean that Weekday starts with zero?
My function will return the foll
GetPYWeekDate(Today()) = 5/3/2011
How to achieve the above using only QV script instead of VBS?
We can easily subtract a day to get the week's start day as Sunday even without using these many conversions, but Week function will not return the correct Week # for the StartDate of the Week
Is there something like AddDays similar to AddMonths & AddYears? I think that may resolve this issue. I don't want to use "+ <days>" to add days
My basic requirement is I want to set the start of the week to be Sunday instead of Monday permanently, and not temporary (still I haven't got the answer).
If that is the case, then only the current year's week number and previous year's week number are tallying, else it is not.
1/1/2012 - Weekstart should be 1 instead of 7 (1st week) - in QV, this is the last week's last day of previous year
and 1/1/2011 - Weekstart should be 7 instead of 6 (prev year's last week) - So 1st week of year 2011 starts on 2nd Jan and not on 3rd
If the above condition satisfies, then only we can get the answer for proper Week over Year value
Both the built-in WeekDay and my function start at 0 and go to 6. With this definition WeekDay for today should be 1 (second day of week) and my function should be 2 (third day of week). You can check this yourself by formatting a listbox with WeekDay as a number (Properties - Number).
But for my function you can easily change this if you want an other value: The Dual function takes two parameters - the second defines the number. So, just change Mod(Date-1,7) to Mod(Date-1,7)+1. It will still work using Sunday as the first day of the week.
The function WeekStart does not change, so it will still return Monday as the first day of the week. (ISO 8601; http://en.wikipedia.org/wiki/ISO_week_date)
+ Dual(DayNumberOfYear(vCYStartDate)) - 1
Is this the way to achieve previous year's weekday using your MyWeekDay?
I am not sure if I understand what you are trying to do... If it is just a matter of getting the weekday of previous years day, then you can use "WeekDay(AddMonths(Date, -12))" or with the alternative Weekday function around the AddMonths function instead.
The Dual function should normally be the outermost function:
<Text that you want to display>,
<Number that is a unique identifier for the day/date>
Btw, there is no function AddDays because you do not need one. You should indeed just use "Date( Date+<days>)".