display/calculate weeks over years without actual date

[#1]

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

[jagan]

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.

[Joaquin_Lazaro]

Hi thoschro

Read this documents published here by HIC about week concepts.

When handling dates you need a master calendar.

Joaquín

[#4]

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.

[preminqlik]

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)

[#6]

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

[#7]

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.

[#8]

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

[jagan]

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.

[#10]

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?