Calculating change in something compared to avg of 3 weeks back

Hi all.
Goal: report showing me for each category, the change in number or
percentage compared to the same category. I will try to explain it:
I have a SQL that returns something like
MyValue | Category | Date
63.61 Cat1 2006-11-30 00:00:00.000
65.51 Cat1 2006-12-07 00:00:00.000
63.31 Cat1 2006-12-14 00:00:00.000
60.51 Cat1 2006-12-21 00:00:00.000
50.01 Cat1 2006-12-28 00:00:00.000
10.71 Cat2 2006-11-30 00:00:00.000
20.91 Cat2 2006-12-07 00:00:00.000
19.61 Cat2 2006-12-14 00:00:00.000
18.51 Cat2 2006-12-21 00:00:00.000
13.01 Cat2 2006-12-28 00:00:00.000
My SQL already made sure that each item gets "grouped" into the time unit
that I wise, in this case that unit is a week.
The reports should end up showing a table, in which I have something like:
Cat1 : +10%
Cat2: -3%
The 10 and 3 % values are calculated by taking the last unit of time (last 7
days) and comparing it to the average of the 3 previous units of time (not
including the last one, so it is basically comparing row 0 to avg of rows
1,2 and 3.)
Now, I thought that I may be able to do it using the Table functions, but
all I managed to do is find the "=Previous(Fields!MyVal.Value)" function.
This would be great if I could do something like Previous(Previous()), but I
cannot. Anyone has a simple way of writing in a table cell a function
calculating the value of the previous 3 cells? I couldn't figure out any way
that lets me refer in one cell in the table other cells in the table, which
kind of sucks cause I expected it to be like Excel in that sense.
Any help (for doing it my way or a completely different way)?
thanksOn Mar 2, 7:27 pm, "csmba" <c...@.nowhere.com> wrote:
> Hi all.
> Goal: report showing me for each category, the change in number or
> percentage compared to the same category. I will try to explain it:
> I have a SQL that returns something like
> MyValue | Category | Date
> 63.61 Cat1 2006-11-30 00:00:00.000
> 65.51 Cat1 2006-12-07 00:00:00.000
> 63.31 Cat1 2006-12-14 00:00:00.000
> 60.51 Cat1 2006-12-21 00:00:00.000
> 50.01 Cat1 2006-12-28 00:00:00.000
> 10.71 Cat2 2006-11-30 00:00:00.000
> 20.91 Cat2 2006-12-07 00:00:00.000
> 19.61 Cat2 2006-12-14 00:00:00.000
> 18.51 Cat2 2006-12-21 00:00:00.000
> 13.01 Cat2 2006-12-28 00:00:00.000
> My SQL already made sure that each item gets "grouped" into the time unit
> that I wise, in this case that unit is a week.
> The reports should end up showing a table, in which I have something like:
> Cat1 : +10%
> Cat2: -3%
> The 10 and 3 % values are calculated by taking the last unit of time (last 7
> days) and comparing it to the average of the 3 previous units of time (not
> including the last one, so it is basically comparing row 0 to avg of rows
> 1,2 and 3.)
> Now, I thought that I may be able to do it using the Table functions, but
> all I managed to do is find the "=Previous(Fields!MyVal.Value)" function.
> This would be great if I could do something like Previous(Previous()), but I
> cannot. Anyone has a simple way of writing in a table cell a function
> calculating the value of the previous 3 cells? I couldn't figure out any way
> that lets me refer in one cell in the table other cells in the table, which
> kind of sucks cause I expected it to be like Excel in that sense.
> Any help (for doing it my way or a completely different way)?
> thanks
I would suggest performing this action as part of the stored procedure
or query that sources the report; since T-SQL is quite a bit more
dynamic. You should probably use either a loop or cursor to parse
through the information to determine the desired output. Hope this
helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer