How to Query data for a certain period?

[antexity]

Hello,

I have a cross-tab report with a Relational DB.



I'm trying to calculate the last 8 weeks AVG. I have defined my variable PERIOD to have a value of 0-8. Week before the current week will be 1, 2 weeks before will be 2 and so on until the 8th week... Since my main query is getting 2 years of data, everything that falls off the period of 1-8 will go to the bucket where PERIOD=0. So, when I use the AVERAGE function for PERIOD, it is getting the average value of my PERIOD from 0-8. What I need is to calculate the average of PERIOD WHERE PERIOD is not equal to 0.

Any help would be great,

Thank you
Jonathan

[JGirl]

Create a 'grouping' column in your query which determines which group the aggregate belongs to.  Something along the lines of
           if period between 1 and 8 then 'group a' else 'group b'

Then place that level in the crosstab.  Then create an aggregate column nested under the grouping that is something like average(measure for grouping). 

Then you can hide the grouping columns in your output if you dont wish for them to appear.

Sorry its a bit short on detail but I dont have cognos in front of me at the moment.  Let me know if you need more info.

[antexity]

Thanks for the reply JGirl

If possible could you elaborate more on your reply please? 

My detail report shows the Period '0' calculation Column on the cross tab  I would like to prevent this report from calculating the Period '0' (avg, 2 years worth of data). I would like to have just the previous 8 weeks of averages.

This is my Period Query

CASE

WHEN [G/L Date]>=[cWeek1Srt] AND [G/L Date]<[cWeek1End] THEN (1)
WHEN [G/L Date]>=[cWeek2Srt] AND [G/L Date]<[cWeek1Srt] THEN (2)
WHEN [G/L Date]>=[cWeek3Srt] AND [G/L Date]<[cWeek2Srt] THEN (3)
WHEN [G/L Date]>=[cWeek4Srt] AND [G/L Date]<[cWeek3Srt] THEN (4)
WHEN [G/L Date]>=[cWeek5Srt] AND [G/L Date]<[cWeek4Srt] THEN (5)
WHEN [G/L Date]>=[cWeek6Srt] AND [G/L Date]<[cWeek5Srt] THEN (6)
WHEN [G/L Date]>=[cWeek7Srt] AND [G/L Date]<[cWeek6Srt] THEN (7)
WHEN [G/L Date]>=[cWeek8Srt] AND [G/L Date]<[cWeek7Srt] THEN (8)

ELSE (0)

END


In the attached screenshot, you will see column Period '0' being calculatedon the right side

If you need more info please let me know

Thank you!
Jonathan

[antexity]

AFter further testing, I changed the ELSE (0) to Null and everything worked..  Thanks for all the help!
Jonathan