Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Could you post a qvw with sample data and explain expected output against that?
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.