Calc by week then day of week using fiscal date table

[#1]

The fiscal date calendar has the following fields "Day YYYYMMDD", Day of Week, Month, Day of Month, Year

What i'm trying to do is do a comparison to the same week, then day of week as this time last year, then to 2009 to 2008. However, the problem is that in 2008 the fiscal date calendar had 53 weeks instead of 52. So my weeks/days do not line up properly.

Does anybody have any advice on the best way to possible write this in an expression to be able to have YOY comparisons? IF you need more info please let me know.

Thank you,

[Anonymous]

Anthony,

It is not a QlikView question - it is not even a technical question. What you really need is a clear business definition, in plain words, what it means "same week last year". Because of the nature of the week, there is no "correct" answer. It is what users wants it to be.

[#3]

Well that is already answered. We have defined the dates to associate with the week. We know week 14 of this year we want to compare to week 14 of last year. Then in the calendar we provide which dates are associated with week 14.

[Anonymous]

Sorry, but it's not a definition. Do you know that QV assignes 01/01/2010 to week 53 - what should it mean in your case? Week 14 is easy, but you need the complete rules that tell you what to do about border situations in the beginning and the end of each year. I cannot give you these rules, and QV can't. It is how users undersatnd it.

[#5]

I think my question has been misunderstood, but I appreciate your help.

[johnw]

Anthony wrote:I think my question has been misunderstood, but I appreciate your help.

OK, let's start over then.

As I understand it, you want to compare the fiscal week (1-53) and fiscal day of each week to the same period in the previous year, and the year before that. Now, I don't know your fiscal week offset, so I'm going to pretend we're using calendar weeks just to show some dates, and I'm further pretending that our "week" is the ISO week used by QlikView (starts on Monday, week 1 always includes Jan 4). Given that, when your user wants to compare week 1 of the past three years, they're asking for this comparison, to compare these dates to each other.

2010 week 1 = Jan 4, 2010 - Jan 10, 2010
2009 week 1 = Dec 29, 2008 - Jan 4, 2009
2008 week 1 = Dec 31, 2007 - Jan 6, 2008

That works fine up through week 52:

2010 week 52 = Dec 27, 2010 - Jan 2, 2011
2009 week 52 = Dec 21, 2009 - Dec 27, 2009
2008 week 52 = Dec 22, 2008 - Dec 28, 2009

But then what do we do for week 53? Only 2009 has a week 53, so what can we compare it to? Is that your question?

If it IS, then Michael is correct. This is not a QlikView question. This is not a technical question. This is USER question. What does the USER want to compare week 53 of 2009 to? There is no right answer. There is only what the user wants to see.

If that's NOT your question, then obviously both Michael and I misread your question, and apparently in the same way. Can you rephrase it? Give some example data? Describe your fiscal calendar? Something like that?

[Anonymous]

Thanks John, you explained it well.
Week is a rather tricky measure and is often defined in different ways. We have a client with a calendar that has 13 weeks in each quarter, but the length of week sometimes may be greater or less than 7 days, and months don't usually start on day one but at the beginning of the week instead. The calendar is uncommon but at the same time is very logical, so it was possible to create a QV rules for this client according to thieir needs.
Anthony, I hope your situation is simpler than I described. Looks like you need to clatify the users's expectations. In the other words, what would they do if there are no computers...

[#8]

I very much appreciate help from both of you. That indeed was an issues that has been addressed by the business. Attached is an example of the issue that I'm having. I hope it describes what my goal is and what is happening well.

Thank you again for any advice you can provide.

Anthony

[#9]

Anthony

Sorry my jumping in by my users had the strangest calander, it did not match ISO, retail link, or normal USA or anything else I have seen. Years lways had 52 weeks and they never alligned the dates back up. So week 1 used to be in Jan and is now Dec 21!

However they gave a excel spread sheet showing the dates and weeks over many years so I just used QV to point to the strange calendar for Year vs. year comparisions.

Hope this helps.

Bruce

[#10]

That's exactly what we do. except our fiscal calendar is generated within our system. I currently use a strait table to generate YOY comparisons.

Below is my code. Essentially, Except instead of looking at day which is mm/dd I want to look at the week, then the day of week. Since mm/dd may not always fall on the same week every fiscal year.

This code calculates the previous year sales.

if(isnull([Fiscal Year]) = -1,sum({$<[Fiscal Year]={2009}>}if(text(Day) < text(vToday), [Sales Amount]*PreviousYear_Flag)), if(trim(text([Fiscal Year])) = 2010,sum({$<[Fiscal Year]={2009}>}if(texT(Day) < text(vToday), [Sales Amount]*PreviousYear_Flag)),
if([Fiscal Year] = 2009, sum({$<[Fiscal Year]={2008}>}if(text(Day) < text(vToday), [Sales Amount]*PreviousYear2_Flag)), if([Fiscal Year] = 2008,0))))