crosstab with date increment grouping

[hd_loki]

Hi,

 

I have created a crosstab which displays session run counts by referrer / date and have successfully set groupings on date for YYYY and YYYY-MM for a given date range but what I would like to do is to have the dates grouped in 7 day intervals rather than an exact week. So in other words, not necessarily Monday to Monday, but in increments of 7 days based on whatever the start_date parameter is (could be any day of the week).

 

Is there a way to do that in a crosstab?

 

Thank you,

 

Heather

[Giulio Toffoli]

The "bucket expression", that's the expression used to group rows or columns in a crossta, is a java expression. When the expression value changes, a new row/column is created.

 

 

When you have a field of type Date and may you want to aggregate a measure (i.e. your run counts) by week: a way to do it is to set as bucket expression for the particular crosstab group somthing that returns the week to which the date belongs to (i.e. the value of the expression could be a string formatted as yyyy-ww) so you'll have 2008-01,2008-02 and so on.

 

To do what you want, you need a function that maps your date in a specific time interval. Suppose you write this function in a class called MyTimeCalculator, you can use as bucket expression something like:

 

MyTimeCalculator.getWeekNumber($P{start_date}, $F{the_date})

 

It's up to you how to decide, based on your start_date parameter passed to the function, to which week the_date belongs to.

 

Giulio

Post edited by: giulio, at: 2008/02/21 21:58

[hd_loki]

Hi,

 

Thanks for the tip. I was able to create a correct bucket expression before grouped by 'yyyy-ww', but I need to make the grouping as 7 day intervals from the start date, regardless of the week.

 

I assume the custom function would create a custom expression to somehow mod off the week of the year based on the beginning expression?

[hd_loki]

Well,

 

I've made a little progress on this date grouping (by non-standard date formats). But have a few more questions if anyone would like to offer some help.

 

What i've done is used a query (created by our resident oracle guru) using the rownum returned along with records in the Oracle RS:

 

select sum(run_count) as run_count,floor(rownum/7),avg(avg_time) as avg_time

from

(select avg_time,run_count

from

stats_mv

where start_time >= $P{psub_startDate} AND start_time < $P{psub_endDate}

order by start_time

)a

group by floor(rownum/7)

 

I created a class DateManipulator which receives the input startDate and int of increment to roll the date the given number of days and return the resulting Date.

 

*But* I am wanting to group by that first and subsequent 7th day and believe I will need to implement the custom incrementer.

 

Am I going in the right direction? I have searched the forum and can only find one instance of code implementing JRIncrementer, is there any documentation regarding the usage of this interface?

 

Thanks for any help