Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello QlikViewers,
I have a question that I just not can find a good solution for.
I have build a MasterCalendar from a specific Excel file from our Customer (The customer has created his own financial calendar in Excel).
I want to create an indicator in the calendar for al the weeks that are this week, 4, 8 and 13 for weeks ago but also the weeks This week, 4, 8 and 13 weeks ago last year.
I want to create an indicator for easy use in set analysis so I can compare week.
Thanks for all the help.
Ah, I see. Here's an example using 4 weeks:
if((MyDateField <= today() and MyDateField >= weekstart(today()-28)) OR (MyDateField <= addyears(today(),-1) and MyDateField >= weekstart(addyears(today(),-1)-28),1) as InCurrent4Week
Regards,
Vlad
Jeroen,
See the function inweek() which allows you to check whether the first parameter date lies within the week that the second parameter date belongs to. This function returns true (-1) if it is the case. So for example:
-1 * rangemin(inweek(MyDateField,today()),inweek(MyDateField,addyears(today(),-1))) as InCurrentWeek
This will return true if the date is in the current week or in the same week last year. It should be pretty easy for you to extrapolate this to do the 4, 8 , 13 week logic. Final note: since it's a fiscal calendar, you may need to use the 3rd parameter of the function to shift things around. See the Help file for more details.
Regards,
Vlad
Vlad,
Thank you for your help.
I didn't know about this function.
Only I can not see how to tune this function so there will be an indicator if a day lays in the past 4 or 8 or 13 weeks ago?
Maybe I have to specify my question a little bit more.
The 4 Weeks Ago Indicator has to mark a date If the date is between today and 4 weeks ago.
The 8 Weeks Ago Indicator has to mark a date If the date is between today and 8 weeks ago.
The 13 Weeks Ago Indicator has to mark a date If the date is between today and 13 weeks ago.
And I want to compare it with the the 4, 8 and 13 weeks last year.
With the InWeek function it will only indicates the dates that are belonging 4, 8 or 13 weeks ago.
Thanks again for your help
Ah, I see. Here's an example using 4 weeks:
if((MyDateField <= today() and MyDateField >= weekstart(today()-28)) OR (MyDateField <= addyears(today(),-1) and MyDateField >= weekstart(addyears(today(),-1)-28),1) as InCurrent4Week
Regards,
Vlad
Vlad,
This is what I was looking for.
The solution is much easier then I was thinking about.
Thank you for your time and help!