Previous year value

building reports webintelligence
1

Hey all,

In a report, I have a break on the region and list values by years. Obviously, the years are selected with prompts to have a BETWEEN so I don’t know which and how many years there will be.

Among many columns, I need to come up with the previous year value to calculate the variation.

For the first year selected, I created another query filtered on this first year -1.

The formula for this “previous year” column looks like this:

=If([Query Display].[Year]=[UserResponseBeginningYear];[Query Prior].[Count];[Query Display].[Count] Where ([Query Display].[Year]=Previous([Query Display].[Year];([Region]))))

So basically, if the year = the entered year then I’ll take the value from the “prior year” query, else… and there I have a problem.

Let’s say the report was filtered for years 2006 to 2009. Besides 2006, I can display the value for 2005 with from the second query. But I can’t find a way to display the 2006 value besides 2007, the 2007 value besides 2008 and so on.

I’ve tried I think every possible way to use the conexte operators, the Previous() function…

Heu… any ideas?

JFS :canada: (BOB member since 2009-04-29)

2

Hi,

some possibilities:
-(report)unlink the dataproviders on the date objects. That way it should be possible to sum the fact with something like [Query Display].[Year]=[Query Prior].[Year]+1
-(universe)create an alias for the fact table, connect it with the same joins as the original fact table, but alter the join to the date table so it joins on the previous year. That way you can easily create [Measure Prev Year] values in your universe (and similar previous week, etc)

highandstoned :netherlands: (BOB member since 2005-08-01)