Compare date ranges

I'm trying to determine if it's possible to compare date ranges in BIRT and if it is how to do it?

What I'm trying to do is compare one weeks/months figures with another to capture trends

Find more posts tagged with

Comments

mwilliams

Can you explain this a little more in detail?

JMcG

Sure, I have a MySQL database containing transactions and I have a requirement to compare one weeks transactions to another in a line graph (Mon vs Mon, Tues vs Tues etc). Total transactions on the vertical axis and dates on the horizontal axis.

It's straight forward to get one weeks data on to a graph but I can't figure out how to get another one superimposed on top (with a different set of dates on the horizontal axis)

I assumed I could use 2 sql statements as data sets but again I can't get them together on the same graph

mwilliams

Can you show a sample of what your data looks like? I'll try to make an example.

JMcG

It's quite a large data set but I'm only extracting the revenue and the date of booking

I am extracting the following SQL set (where BOOKINGDATE is a date time object and revenue is a decimal)

select BOOKINGS.BOOKINGDATE, BOOKINGS.REVENUE
from BOOKINGS
where BOOKINGS.BOOKINGDATE >= '2012-08-01 and BOOKINGS.BOOKINGDATE <= '2012-08-07'

and trying to compare it to this results set

select BOOKINGS.BOOKINGDATE, BOOKINGS.REVENUE
from BOOKINGS
where BOOKINGS.BOOKINGDATE >= '2012-08-08 and BOOKINGS.BOOKINGDATE <= '2012-08-14'

I am then looking to overlay the two result sets over each other.

I am new to BIRT so I may be going at this from the wrong direction entirely

Uuux

As I am quite nee to BIRT aswel I think you might want to use Joint Dataset which can help you with joining two separated datasets. But as you say the amount of data is huge it takes quite some time to generate the report so it is always better to let handle huge data to SQL server by optimalizing your query, maybe create some function.

mwilliams

The best way will be to combine those two dataSets into a single query, bringing both weeks in. You could do a Union in your query, and make a 3rd column in each result set, before joining them. In the first select statement, you'd assign "week 1" to the field, in the second statement, you'd assign "week 2". Then, you could use this new field in the optional grouping expression to separate the weeks into different series. I'll make a quick example and post it in here.

mwilliams

Something like this.

bgbaird

Another possible solution that I've used:

Define a computed column for the week of the year. BirtDateTime.week()

Brian

mwilliams

Thanks, Brian!

JMcG

Hi Guys

Thanks for that I got both solutions to work

John

mwilliams

No problem. Let us know whenever you have questions!