Last four week average

system · June 27, 2003, 4:22am

Hi guys,

Could you help me resolve this issue on the reporter

My user is requesting last four week average for all weeks in a year.

My data is

Week >> week Index >> Measure1 >> Last four weekaverage
2001W01 >> 53 >> 10 >> 25
2001W02 >> 54 >> 20 >> 25
2001W03 >> 55 >> 30 >> 25
2001W04 >> 56 >> 40 >> 25
2001W05 >> 57 >> 10 >> 25
2001W06 >> 58 >> 20 >> 25
2001W07 >> 59 >> 30 >> 25
2001W08 >> 60 >> 40 >> 25
2001W09 >> 61 >> 10 >> 25
2001W10 >> 62 >> 20 >> 25
2001W11 >> 63 >> 30 >> 25
2001W12 >> 64 >> 40 >> 25
2001W13 >> 65 >> 10 >> 25
2001W14 >> 66 >> 20 >> 25
2001W16 >> 68 >> 40 >> 18.75

In the above data Lastfourweekaverage is the column which i need to calculate and show up on the report.

here the last 4 week average can be calculated as sum of measures where ( Week index of current row + (Week index of current row -1) +(Week index of current row -2) + (Week index of current row -3))

I cannot use function because if i dont have data for a week in between say week 15 does not have data then my average for 16 is (w16+W14+W13+W12)/4 which is wrong i always want my week 16 average as ( w16+W15+W14+W13)

I have created this weekindex object so that i can just subract (1) on the index to get the previous value of that weekindex.

This logic is mission critical as i have reports where in we use last 4 week average ,8 week average and 13 average and compare this quarter sales to sales last year same quarter, Compare quarterly sales to previous quarter sales and so many other calculations using the time related functions like previous month, year and so on.

Ur help in this regard is appreciated.

Thanks in Advance
Ramnath

ramknath (BOB member since 2002-09-24)

system · June 27, 2003, 2:24pm

How about this:

Create a WeekNumber variable that will parse out the week number from your Week object. Use Substr(,5,2) or Right(,2). Either will do the job.
Create your Average variable with this logic: Average() Where (-4 >= ).

Give that a shot.

MichaelWelter (BOB member since 2002-08-08)

system · July 4, 2003, 6:54am

Dear Michael,

Average() Where (-4 >= ).

This will give a syntax error as we cannot right hand side of where clause cannot be variable…

Thanx
Ramnath

ramknath (BOB member since 2002-09-24)

system · July 4, 2003, 1:54pm

Please, check out the Reporter FAQ for a workaround…

Andreas (BOB member since 2002-06-20)

system · July 7, 2003, 5:41pm

In the FAQ, go down to this question/answer – what you have to do is to compute an intermediate variable, and build your WHERE on that.

Anita Craig (BOB member since 2002-06-17)

system · July 8, 2003, 2:51am

Dear Anita & Andreas,

Thanks for pointing me to the FAQ. I was able to upgrade myself to handle Where clause in better way.

On the other hand I am still unable to figure out a way to get last four weeks average for all weeks in a year where i might have some weeks with no data. The “previous(measures)” command does not allow me to include missing values.

I have a logic but unable to create a report in that format. Below given is the report logic.

I cannot create outer joins in the Universe as the Database size is huge and data is very low granularity compared to this report.

Report Logic
Data source 1
Product category, Customer Group, Week, Sales
Where (All weeks in year less or equal to Prompt week)

Datasource 2
Weeks
Where (All weeks in year less or equal to Prompt week)

Now i need a variable which will give me 
If datasource1.sales is blank then zero else value
in the report i will use Datasource2.week so that i can get an outer joint on the report.

Second step i want to use Previous to compute the sum of last week based on Datasource2.week which means i dont have value for previous week then the previous should return 0.

Has any body had a problem of this sort.

Regards
Ramnath

ramknath (BOB member since 2002-09-24)

system · July 8, 2003, 9:11am

I believe that you are stretching the abilities reporter has beyond the edge, this is more of a thing to handle at the database level.

A possible strategy would be to create a special calender file on weekly basis that holds the relative weeks , relative against the current week and use this to bring in the 4 latest weeks REGARDLESS if this results in data being brought in for all 4 weeks or on only three weeks.

Let’s say that current week will get label ‘CW’ and we have weeks 1 to 53 in a year (depends on how week calculation is defined) we can create a view that holds the weeknumbers from this week and the three preceding ones:

Select WEEK(CURRENT DATE) AS WEEKNO, ‘CW’ AS REL_WEEK from SYSIBM.SYSDUMMY1
UNION
Select
CASE
WHEN WEEK(CURRENT DATE)= 1 THEN 53
ELSE WEEK(CURRENT DATE)-1 END AS WEEKNO
, ‘1W’ AS REL_WEEK from SYSIBM.SYSDUMMY1
UNION
Select
CASE
WHEN WEEK(CURRENT DATE)= 1 THEN 52
WHEN WEEK(CURRENT DATE)= 2 THEN 53
ELSE WEEK(CURRENT DATE)-2 END AS WEEKNO
,‘2W’ AS REL_WEEK from SYSIBM.SYSDUMMY1
UNION
Select
CASE
WHEN WEEK(CURRENT DATE)= 1 THEN 51
WHEN WEEK(CURRENT DATE)= 2 THEN 52
WHEN WEEK(CURRENT DATE)= 3 THEN 53
ELSE WEEK(CURRENT DATE)-3 END AS WEEKNO
,‘3W’ AS REL_WEEK from SYSIBM.SYSDUMMY1

(This is in DB2, ORACLE would use DUAL)

Add this view in universe to facttable using an outer join (no problem with just 4 records) and fetch the object REL_WEEK in the report.

Perhaps this will give something to play with …

blom0344 (BOB member since 2002-09-04)

system · July 9, 2003, 7:38am

Thanks T.Blom.

This is an Impressive Idea I am giving it a shot right away… Three Cheers.

Regards
Ramnath

ramknath (BOB member since 2002-09-24)

system · July 14, 2003, 6:06am

Hi Guys,

Thanks for all the support.

I was able to resolve the problem of finding the four week average using some the logics given from the forum. Here is how i achieved it.

Solution 1:

I created a table generating two fields

Lastfourweekave, Weeknames
WeekName1, WeekName1
WeekName1, WeekName of (WeekName1 -1)
WeekName1, WeekName of (WeekName1 -2)
WeekName1, WeekName of (WeekName1 -3)
This repeats for all weeks in the year

Add this table in the universe joining my Calendar table WeekField to Weeknames field in the new table.

Created an object Lastfourweekcumulative with a select statement
Lastfourweekave

Now in reports where i needed the four week average Selected Lastfourweek rather than the Weekfield object.

The values shown in the measures now are aggregates for week and 3 weeks prior to it.

Solution 2:

I just created the last four week sum by creating a calculated measure

Datasource1

Select all dimensions, week (all weeks in a year),Salesmeasure

Datasource2
Select Week(all weeks in a year)

Link datasource1 and Datasource2 for dimension week

Calculated Measure

1. SalesMeasureallweeks
if(isnull(Multicube(SalesMeasure)) then 0 else Multicube(SalesMeasure).

2. Lastfourweekssales
(SalesMeasureallweeks + Previous(SalesMeasureallweeks) + Previous(Previous(SalesMeasureallweeks)) + Previous(Previous(Previous(SalesMeasureallweeks))))/4

this would bring give zero for weeks for which there was no data in the database.

Thanks for all the support without which i could not have solved this problem.

Regards
Ramnath

ramknath (BOB member since 2002-09-24)