topic Re: How to create week fields with dynamic input date in QlikView

How to create week fields with dynamic input date

Anonymous — Thu, 27 Dec 2012 10:14:57 GMT

Hi guys can anyone help?

I am trying to archive the following but not sure how to go about it:

I need to create week fields as:

T+1 = Prior to current week

T-0 = Current week

T-1 = one week from the current week

T-2 = two weeks from the current week

T-3 = three weeks from the current week

T-4 = four weeks from the current week

T-5 = five weeks from the current week

T-6 = six weeks from the current week

The 'T-0' (current week) needs to be a dynamic variable where the user enters the date in to an input box they wish to report on and T-1, T-2, etc change accordingly to whatever date has been entered.

Thanks in advance

Re: How to create week fields with dynamic input date

Anonymous — Thu, 27 Dec 2012 14:40:15 GMT

I would use something like the following:

SET T1Start = "=WeekStart(vSelectedDate+7)";

SET T1End = "=WeekEnd(vSelectedDate+7)";

SET T0Start = "=WeekStart(vSelectedDate)";

SET T0End = "=WeekEnd(vSelectedDate)";

SET Tm1Start = "=WeekStart(vSelectedDate-7)";

SET Tm1End = "=WeekEnd(vSelectedDate-7)";

SET Tm2Start = "=WeekStart(vSelectedDate-14)";

SET Tm2End = "=WeekEnd(vSelectedDate-14)"; ... etc ....

Jonathan

Re: How to create week fields with dynamic input date

Thu, 27 Dec 2012 14:57:52 GMT

Create a calendar object and connect the value of calendar object to a variable

For example if the variable name is vVar

you can define the other varibles as follows

Let TCurr= Weekstart(vVar);

Let TPrev=WeekStart(vVar,-1);

Let Tafter =WeekStart(vVar,1);

and so forth

Re: How to create week fields with dynamic input date

Anonymous — Thu, 27 Dec 2012 15:14:30 GMT

H Pauljin, how would I get the value of a calendar object and assign it to a variable?

Re: How to create week fields with dynamic input date

Thu, 27 Dec 2012 15:56:16 GMT

Right click New Sheet Object > Slider/Calendar Object

Remember to create the variable before creating the calendar object

Look at the file I attached to see the properties of calendar object

Re: How to create week fields with dynamic input date

Anonymous — Fri, 28 Dec 2012 09:47:29 GMT

Instead of creating variables is there a way of creating each of the weeks as fields instead?

Re: How to create week fields with dynamic input date

Anonymous — Fri, 28 Dec 2012 11:02:44 GMT

Taken from p339 of Reference Manual:

week(date)

Week number. Returns an integer representing the week when the fraction of expr is interpreted as a date

according to the standard number interpretation.

Example:

week( '1971-10-30' ) returns 43.

If the date format used does not correspond to the one set in the operating system, QlikView will not be able

to make a correct interpretation. See above under day(date) (page 339).

Re: How to create week fields with dynamic input date

Anonymous — Fri, 28 Dec 2012 11:21:30 GMT

Instead of creating variables is there a way of creating each of the weeks as fields instead? as i will need to use the weeks (T0-T6) as dimensions in charts

Re: How to create week fields with dynamic input date

Anonymous — Fri, 28 Dec 2012 14:02:26 GMT

If you are happy to just analyse the previous weeks based upon the date of the document reload, then you can create a new WeekType field in your calendar like this (with TempDate being the date you base the creation of your calendar upon ... see post http://community.qlik.com/message/291974#291974😞

if(week(TempDate)=Week(Today()),'T0',

if(week(TempDate)=Week(Today()-7),'Tm1',

if(week(TempDate)=Week(Today()-14),'Tm2', ..... etc

))) AS WeekType,

It gets tricky however if you still want the application to work with a dynamic date, where the user can change the analysis date using a calendar. You would have to create a seperate table linked to your calendar table, which has the different dates that correspond to the previous weeks for each date in the calendar. If this is the case then variables are safer as having multiple dates is confusing.

Jonathan

Re: How to create week fields with dynamic input date

Anonymous — Sat, 29 Dec 2012 11:36:46 GMT

I think the using variables will be easier. The user wants to use an input box in the UI where they enter the date, this date then needs to be stored into a variable and the weeks then change or what ever date has been input. How do I create this input box that stores the date into a dynamic variable?

I have had tried a few things but it doesn't seem to work.

Thanks again

Re: How to create week fields with dynamic input date

Anonymous — Sun, 30 Dec 2012 22:48:40 GMT

Right-click > New sheet object > Input box

Select new Variable

Jonathan

Re: How to create week fields with dynamic input date

Anonymous — Wed, 02 Jan 2013 10:07:30 GMT

Hi guys I think i am almost there i have created the following:

SET TCurr= WEEKSTART($(vVar)); //T-0

SET TPrev= WEEKSTART($(vVar),-1); //T+1

SET TAfterOne= WEEKSTART($(vVar),+1); //T-1

SET TAfterTwo= WEEKSTART($(vVar),+2); //T-2

SET TAfterThree= WEEKSTART($(vVar),+3); //T-3

SET TAfterFour= WEEKSTART($(vVar),+4); //T-4

SET TAfterFive= WEEKSTART($(vVar),+5); //T-5

SET TAfterSix= WEEKSTART($(vVar),+6); //T-6

The problem I am having now is WEEKSTART function is giving me all dates from the Start of the week (Monday)

How can I change this so that the start of the week can be any day the user selects? e.g if the user selects a date that falls on a Wednesday then all the days will changing according to that? and the Wednesday will taken as the start of the week.

Re: How to create week fields with dynamic input date

Anonymous — Wed, 02 Jan 2013 10:28:39 GMT

WeekStart takes a 3rd parameter (weekoffset), which you can make a configurable variable: See Reference manual, page 349 (Qv11):

WeekStart(date [, shift = 0 [,weekoffset = 0]])

Returns a value corresponding to a timestamp with the first millisecond of the first date ( Monday ) of

the calendar week containing date. The default output format will be the DateFormat set in the script.

Shift is an integer, where the value 0 indicates the week which contains date. Negative values in

shift indicate preceding weeks and positive values indicate succeeding weeks. If you want to work

with weeks not starting midnight between Sunday and Monday, indicate an offset in days in weekoffset. This may be given as a real number indicating days and/or fractions of a day.