Latest 7 Days in a Crosstab

building reports webintelligence
1

Hi, I have a query that returns data like this:

Date Market Sales
1/1/09 US 50
1/2/09 UK 100
etc. for several years (and I need to bring back several years of data for some trend charts)

I’d like a crosstab table that shows sales for each market for the last 7 days (last 7 days being dynamically chosen based on data returned by the query), like this:

Market 4/30/10 5/1/10 5/2/10 5/3/10 5/4/10 5/5/10 5/6/10
US 25 etc
UK 60 etc
etc

The filter option only lets me select specific dates, not anything like the ‘most recent 7’. Rank won’t work as I’d be ranking the date dimension on itself right?

If I try and make a variable based on Date it forces it to be a measure and not a dimension

Is this possible without changes to the Universe?

Thanks!

Steve77 (BOB member since 2010-04-05)

2

add a new data provider which returns only last 7 days data.
provide a prompt for dynamic selection of date.

Rakesh_K :india: (BOB member since 2007-12-11)

3

Or … if you don’t want to do that you can create a new variable that works out the daysbetween() between your data’s date and currentdate(0

Test=daysbetween([your data’s date];currentdate())

apply a filter to your crosstab based on that variable where its 7 or under = your last seven days. :slight_smile:

SteveD :uk: (BOB member since 2009-11-02)

4

Hi,

Ideally I think you should follow the advice provided by Rakesh_K and add a new data provider to bring in the last 7 days. It would be great if you maintained a table in your database that would provide you with various date formats, future and past dates. As you never know what users will ask for next.

e.g.

‘Dates table’ :

Col1 Col2 Col3 Col4 col5
01-MAY-10 5/1/10 MAY Sunday 20100501

With this table, you could incorporate it in your universe and build a filter in the universe on the Col5 (for example) which you could then use when building you report.

Lets say that is long term and you are looking for quick and dirty, you could also try the following:

(I am assuming an Oracle Dbase)
Choose to modify the report, proceed to the Edit Query panel. View the SQL and then select the “use custom SQL” option. Use the SQL below to create a new table which you can then join to the original SQL statement, thus limiting your result set to 7 days.

select date_code from
(
select to_char((sysdate-6), ‘mm-dd-yy’) date_code from dual union
select to_char((sysdate-5), ‘mm-dd-yy’) from dual union
select to_char((sysdate-4), ‘mm-dd-yy’) from dual union
select to_char((sysdate-3), ‘mm-dd-yy’) from dual union
select to_char((sysdate-2), ‘mm-dd-yy’) from dual union
select to_char((sysdate-1), ‘mm-dd-yy’) from dual union
select to_char((sysdate), ‘mm-dd-yy’) from dual
) new_table

scoreyh2o :jamaica: (BOB member since 2008-03-29)

5

Well, you could absolutely do with a calendar table pretty well always in your universe :yesnod: but more data providers can = sluggishness :nonod: so I would usually try to minimise the amount of data being brought back and do as much as possible with it. Having a calendar table is a godsend though because it makes finding ranges of dates easier, if you return an object that returns the date 7 days ago, yes that’s easy to reference but then what’s the difference between that doing a test on your dates?

SteveD :uk: (BOB member since 2009-11-02)

6

scoreyh2o has suggested try code as lyk below

Rajubollas :india: (BOB member since 2009-09-04)