Help with displaying the correct week difference

[helen_pip]

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

[sunny_talwar · Accepted solution]

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;

[adamdavi3s]

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

[helen_pip]

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

[sunny_talwar]

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