Getting data from one column and displaying in two different columns in imprompt

[ananya18]

Hi All:
I am working on requirment which is as below

I need to get revenue for current year in one column and previous year in another column,but the date in the table is like this

Date               Rev
03-03-2008    200
03-03-2007    100

The report should get data as below when user selects date range from 03-03-2007 to 03-03-2008

current Year    Rev   Last Year     Rev
03-03-2008    200   03-03-2007     100

How can i achieve this.appriciate your inputs ,advance thanks for your help.

[almeids]

You only show 2 dates in the report example, does the report need to list individual dates and revenue in 2 pairs of columns, or does it need to summarize the revenue for each year in the range and present it in a single row?

In general, to flatten out your data horizontally you need to create conditionally calculations for your columns.  For example, create a "last year revenue" calculation with an expression something like (this is pseudocode, substitute Impromptu or database syntax/functions):

if (year(Date_from_database)=last_year) then (Revenue) else (0)

You'll need to somehow determine "last_year" from prompt values, it's not clear how that should work if for example the user selects a range within a single year.

[ananya18]

Thanks for your reply ,yes it need to summarize the revenue for each year in the range and present it in a single row. Any suggestions please?

[almeids]

You have my suggestion...create calculation columns for each year which test for the appropriate year and return either the Revenue value or zero as appropriate.  This assumes you are always dealing with 2 years (or any fixed number).

If the number of years can vary, your report should be a crosstab with year (extracted from the date) as the columns.  You could also use this approach for a fixed number of years and avoid the calculated columns, but as I recall crosstabs could be troublesome in Impromptu (though perhaps they've improved in recent years) and limited your formatting options.

[damienm]

Hi I have a similar report, to work out the variance on year what would be the expression in a cal field?

[almeids]

Um... year_2_value - year_1_value?
Sorry if that's overly simplistic but you didn't give us a lot to go on.

[guruji]

Hi,

Its simple to have a 2 different promts in that if the years of data is not limited else you can have calculated column.

Regards
Guruji

[tushtech]

i have a related problem..

i have to select year from the prompt and have to display actuals for the year year -1 as well as year-2

2006 FY Actuals 2007 FY actuals 2008
1.2                   1.3                   2.3

lik dis... please let me know how to go about this.