topic Re: display/calculate weeks over years without actual date in QlikView

display/calculate weeks over years without actual date

Thu, 06 Mar 2014 08:09:06 GMT

Hello community,

I have week, month and year as fields (no actual dates). Week and month are not related (two different reports) thus exclude each other.

How can I show the last 12 weeks (starting with the selected week) in a pivot and also considering the weeks of 2013?

I tried concatenating Year & Week but it didn't work.

Do I have to create a master calendar or is there some "easier" way?

If master calendar is the only way how should I go about it?

Week/Month data: [1,2,...,10,11,...]

Thank you,

Thorsten

Re: display/calculate weeks over years without actual date

jagan — Thu, 06 Mar 2014 08:29:12 GMT

Hi,

Try MakeWeekDate() in qlikview like this

=MakeWeekDate(Year, WeekNo, 1) -- Will return the week start date.

LOAD

MakeWeekDate(Year, WeekNo, 1) AS DateDimensionName

FROM Data;

Now in set analysis you can filter the last 12 weeks by using this

=Sum({<YearDimensionName=, WeekDimensionName=, DateDimensionName={'>=$(=WeekStart(Max(DateDimensionName), -11))<=$(=WeekEnd(Max(DateDimensionName)))'}>} MeasureName)

Hope this helps you.

Regards,

Jagan.

Re: display/calculate weeks over years without actual date

Joaquin_Lazaro — Thu, 06 Mar 2014 08:29:41 GMT

Hi thoschro

Read this documents published here by HIC about week concepts.

When handling dates you need a master calendar.

Joaquín

Re: display/calculate weeks over years without actual date

Thu, 06 Mar 2014 08:30:53 GMT

I had this recently, though whilst not exact, it's "similar". The raw data only gave us a Year and WeekNo - ie (1352) so we needed to convert this back.

It may help, but this is the expression to convert I used.

=Date(IF((WEEKDAY(MakeDate(LEFT([YYWW],2)+2000))>4), ((MakeDate(LEFT([YYWW],2)+2000)+(7-WEEKDAY(MakeDate(LEFT([YYWW],2)+2000))))+((RIGHT([YYWW],2)-1)*7)), ((MakeDate(LEFT([YYWW],2)+2000)-(WEEKDAY(MakeDate(LEFT([YYWW],2)+2000))))+((RIGHT([YYWW],2)-1)*7)) ),'DD/MM/YYYY') as [YYWW], [YYWW]);

Hope it helps.

Re: display/calculate weeks over years without actual date

preminqlik — Thu, 06 Mar 2014 08:34:00 GMT

hi try like this

autonumber(Year&num(Week,'00')) as Year_Week

and then in front end

sum({<Year_Week={'>=$(=max(Year_Week)-12)<$(=max(Year_Week))'}>}Sales)

Re: display/calculate weeks over years without actual date

Thu, 06 Mar 2014 08:47:52 GMT

I have week, month and year as fields (no actual dates). Week and month are not related (two different reports) thus exclude each other.

if your date fields are not related how would you correctly associate selections on dates and show previous trend behaviour?

If week, month and year are all based off the same date field i.e.

Week(Date)

Month(Date)

Year(Date)

Then simply

=Sum({<Week={'>=$(=WeekStart(Max(Date), -11))<=$(=WeekEnd(Max(Date)))'}>} MeasureName)

Cheers,

Byron

Re: display/calculate weeks over years without actual date

Thu, 06 Mar 2014 09:39:44 GMT

Hi Jagan,

I tried your solution. It works really nice for the weeks. I now have 2013/51 , 2013/52, 2014/1 and so on.

How can I now turn the months to look and work like the weeks since I don't see a function for this and I also can't use the week dimension?

Thanks again.

Re: display/calculate weeks over years without actual date

Thu, 06 Mar 2014 09:46:12 GMT

If you have weeks and years, try my initial post below...

Re: display/calculate weeks over years without actual date

jagan — Thu, 06 Mar 2014 09:54:51 GMT

Hi,

Since you arrived Date from the Week and Year, it is easier to get the month like this

LOAD *,

Month(DateDimensionName) AS Month;

LOAD

MakeWeekDate(Year, WeekNo, 1) AS DateDimensionName

FROM Data;

Now in set analysis you can filter the last 12 weeks by using this

=Sum({<YearDimensionName=, WeekDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName), -11))<=$(=MonthEnd(Max(DateDimensionName)))'}>} MeasureName)

The above expression will give you last 12 months values.

Regards,

Jagan.

Re: display/calculate weeks over years without actual date

Thu, 06 Mar 2014 14:41:55 GMT

I can't use Week for the Month values because they exclude each other. Isn't there another way to use the year and month fields and still use a variable to select the range for going back in time over years?

Re: display/calculate weeks over years without actual date

jagan — Fri, 07 Mar 2014 02:10:28 GMT

Hi,

Can you attach the sample file and come up with an example what you need exactly.

Regards,

Jagan.

Re: display/calculate weeks over years without actual date

Fri, 07 Mar 2014 12:29:05 GMT

I was able to get what I want after creating a calendar and linking that month with my month field.

Then in the expression I wrote:

sum({<Year=, CalendarMonth=, Date={'>=$(=addmonths(max(Date), - ($(vSetCalendarRange)-1)))<=$(=addmonths(max(Date),0))'}>} (Amount))

which gives me the right range based on the month selection.

Thank you all for the details.