topic Create Indicator for 4, 8, and 13 weeks ago in Calendar in QlikView

Create Indicator for 4, 8, and 13 weeks ago in Calendar

jjordaan — Thu, 06 Dec 2012 17:34:50 GMT

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.

Re: Create Indicator for 4, 8, and 13 weeks ago in Calendar

vgutkovsky — Thu, 06 Dec 2012 20:36:45 GMT

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

Re: Create Indicator for 4, 8, and 13 weeks ago in Calendar

jjordaan — Fri, 07 Dec 2012 07:38:41 GMT

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

Re: Create Indicator for 4, 8, and 13 weeks ago in Calendar

vgutkovsky — Fri, 07 Dec 2012 13:05:16 GMT

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

Re: Create Indicator for 4, 8, and 13 weeks ago in Calendar

jjordaan — Fri, 07 Dec 2012 13:31:09 GMT

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!