Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
H Pauljin, how would I get the value of a calendar object and assign it to a variable?
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
Instead of creating variables is there a way of creating each of the weeks as fields instead?
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).
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
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
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