Weekly Average Adjusted by Year

[tlebang001]

Hi all,

I am looking to create a line in a chart. The variables that I am using are Sales, Week and Year. The dimension is Date - I have this over a period of around 4 years.

The line should be the average Sales for each week plus a term which is the difference between that year's average Sales and the overall average. I also do not want this line to be affected by filters.

I know how to get the overall average, I would use something like:

     Sum({1} TOTAL [Sales])/Count({1} TOTAL DISTINCT [Date])

But I am unsure of how to proceed with the rest. Any help would be appreciated!

[tresesco]

Could you post a qvw with sample data and explain expected output against that?

[tlebang001]

In this file the Overall Average and the Yearly Average are shown.

The way I have calculated the Yearly Average would be the same as the way I would get the Weekly Average.

Basically what I am trying to get is a line which does:

     Weekly Average + (Yearly Average - Overall Average)

Would appreciate any responses. Thanks.