Help with displaying the correct week difference

helen_pip — Thu, 22 Sep 2016 10:05:12 GMT

Dear Qlikview user

I have created a field in my load script called MonthDiff which calculates the difference between the same field (Named differently). I then place a where clause on table and filter out months which were have a MonthDiff of <=11.

Month:

Load MonthYear,

AsOfMonth,

Round((AsOfMonth-MonthYear)*12/365.2425) as MonthDiff,

Year(AsOfMonth)-Year(MonthYear) as YearDiff

Resident tmpAsOfCalendar

Where AsOfMonth >= MonthYear

and Round((AsOfMonth-MonthYear)*12/365.2425) <=11;

My results are what I require and are as follows:

AsOfMonth MonthYear MonthDiff

Sep-16 Sep-16 0

Sep-16 Aug-16 1

Sep-16 Jul-16 2

etc....Up to a MonDiff of 11 months

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I am trying to adapt the same approach for WeekDiff and have basically used the same code, but changed the fields to be week by date fields I.e. 12/09/2016 and 19/09/2016

My code is as follows:

[As-Of Calendar_Week]:

Load Week_By_Date,

AsOfWeek,

Round((AsOfWeek-Week_By_Date)*12/365.2425) as WeekDiff,

Year(AsOfWeek)-Year(Week_By_Date) as YearDiff_Week

Resident tmpAsOfCalendar_Week

Where AsOfWeek >= Week_By_Date

and Round((AsOfWeek-Week_By_Date)*12/365.2425) <=13;

Drop Table tmpAsOfCalendar_Week;

However my WeekDiff field is not behaving in the same way as my MonthDiff field and I am getting duplicate results I.e.

I would expect the following for 19/09/2016

AsOfWeek Week_By_Date WeekDiff

19/09/2016 19/09/2016 0

19/09/2016 21/09/2016 1

19/09/2016 05/09/2016 2

etc......Up to a WeekDiff of 13 weeks

However instead I am getting duplicated rows

AsOfWeek Week_By_Date WeekDiff

19/09/2016 19/09/2016 0

19/09/2016 21/09/2016 0

19/09/2016 05/09/2016 0

19/09/2016 19/09/2016 1

19/09/2016 21/09/2016 1

19/09/2016 05/09/2016 1

19/09/2016 19/09/2016 2

19/09/2016 21/09/2016 2

19/09/2016 05/09/2016 2

Could someone kindly guide me on either

A) Adapt my WeekDiff code

B) Suggest I do a group by on WeekDiff field, so I do not get duplicated rows

Any help would be greatly appreciated

Kind Regards

Helen

Re: Help with displaying the correct week difference

sunny_talwar — Thu, 22 Sep 2016 10:29:24 GMT

How about this:

[As-Of Calendar_Week]:

Load Week_By_Date,

AsOfWeek,

Round((AsOfWeek-Week_By_Date)/7) as WeekDiff,

Year(AsOfWeek)-Year(Week_By_Date) as YearDiff_Week

Resident tmpAsOfCalendar_Week

Where AsOfWeek >= Week_By_Date

and Round((AsOfWeek-Week_By_Date)/7) <=13;

Drop Table tmpAsOfCalendar_Week;

Re: Help with displaying the correct week difference

adamdavi3s — Thu, 22 Sep 2016 10:35:16 GMT

Hi Helen,

For your week diff do you need to assess just the date which is the end of the week?

E.g. :

Load weekend(AsOfWeek) as Week_By_Date,

AsOfWeek,

This should mean you can just test every date with one date of that week rather than testing every date with every date

Re: Help with displaying the correct week difference

helen_pip — Thu, 22 Sep 2016 10:50:10 GMT

SunnyT

Thank you so much. I sometimes cannot see the wood through the Trees!

Thanks so much...I will be able to hit my deadline now!

Thanks

Helen

Re: Help with displaying the correct week difference

sunny_talwar — Thu, 22 Sep 2016 10:54:50 GMT

Awesome, its always a good feeling when you are able to get done on or ahead of time. I am glad you did.

Best,

Sunny

topic Re: Help with displaying the correct week difference in QlikView

Help with displaying the correct week difference

Re: Help with displaying the correct week difference

Re: Help with displaying the correct week difference

Re: Help with displaying the correct week difference

Re: Help with displaying the correct week difference