BusinessObjects Board

previous month/week values for more than a YEAR.

building reports desktopintelligence
#1

Hi Friends,

I am trying to show the previous month numbers for a particular ‘CODE’ in a report. Unfortunately, If there are 2 months/weeks I could able to show the numbers.But how do I show the numbers(Previous Month/Week) for more than a year period?.

I tried using the PREVIOUS function, But it’s giving me the wrong result. Instead of getting the previous numbers for the code, it’s showing me the numbers from previous row.

I know I need to define a context and create some variables to get this done…But I am stuck now… :confused:

Could anyone help in how do I proceed.

The report is already has 2 breaks in it and I need to show PREVIOUS month values for a particular CODE in a CLASS break.

Currently I am having a single Query with the values from 01/01/2000 to till date.

The report looks like…

Class1
01/01/2000 CurrMonth Values Prev Month Values
A 1000 0
B 2000 0
C 1100 0
03/01/2000
B 3000 2000
C 1000 1100
08/01/2001
A 5000 1000
Class2
05/01/2000
C
03/01/2001
B
D
08/01/2001
D
Etc…

Looking for some help in finding a solution.

*** I did gone through the archives, But seems to be none of them are helpful to me.

Thanks in Adv.

suntra (BOB member since 2003-02-06)

#2

“previous” function gives you previous record value
You have to use date functions, to get the previous month.
create two variables
=Month()
=Month(LastDayOfMonth(-31))

Hope it would Help :idea:

Shahubar Sadiq :india: (BOB member since 2003-02-28)

#3

Hi,

You could achieve this by creating a previous month object using add_months function (for eg.to_char(add_months(sysdate,-1),‘MM-YYYY’) and now use this object in the 2nd DP (Prev.Month) this DP will give you the data for the previous month and then pull appropriate objects from the current and previous DPs.

Hope this will work,

thanks,

tech_sk8rboy (BOB member since 2003-12-10)

#4

Hi Guys,

Thanks for the replies.

I had already tried those options. The problem with my report is it’s fetching the records for more than 1 year period.

I need to check whether if the previous month numbers exixts or not.
If exists, How do I create a variable ?.

= where <WHAT CONDITION/CONTEXT> I should mention?.

B’z it’s not liking any.

I tried the below,

= where <Rpt_Date> = <Prev_Rpt_Date>

= In (, <Prev_Rpt_Date>)

With the second DP option, It’s not getting the exact numbers for the previous month as the report has a break on the <Rpt_Date> from DP1. And there is no link between the report dates between 2 DP.

Any help would be appreciated.

The requirement is to show a period of monthly values in a report by showing the previous month values on the current month row.

Thanks in Adv.

suntra (BOB member since 2003-02-06)
#5

Please, see this work around in the FAQ Reporter…

Andreas :de: (BOB member since 2002-06-20)

#6

Thanks Andreas for the reply.

I tried the below…

var_flag = <Curr_Rpt_dt> > <Prev_Rpt_Dt>

where (<var_flag> = 1).

It’s still fetching me the current month measures numbers.

As my report has data for more than 3 years, I am bit confused how this going to work.

Any help.

Thanks in Adv.

suntra (BOB member since 2003-02-06)

#7

If the table has “break” or dataproviders not linked then it is a challenge to achieve this and also we cannot use “where” clause with variables and it required hardcoded value.

You can try this, it might work,
In the second data provider, get the summay records with month and year (no details), then use “previous” function to get the previous month value.

Shahubar Sadiq :india: (BOB member since 2003-02-28)

#8

I mean, second data provider should contains,
class,
MonthYear [create one object in the universe to_char(date,‘mon-yy’)]
value

Good luck

Shahubar Sadiq :india: (BOB member since 2003-02-28)

#9

Hi Sadiq,

I’m getting the second DP with Class,Code and the measure based on the NextMonth Report date (Which is a current object in the Universe).

The problem is, When I am trying to show the value from the 2nd DP along with the 1st DP, it’s getting the cumulative totals, but not by Code and previous rpt date.

That is the reason, I was trying to create a flag as Andreas suggested. But seems that is also didn’t work for me.

Thanks.

suntra (BOB member since 2003-02-06)

#10

Brief sketch:

  • Create conformed “Calendar” dimension table

  • Alias your “Income” fact table" name it “alias_Income_LastMonth”

  • Join “Income” to “Calendar” table

  • Join “alias_Income_LastMonth” to “Calendar” table using a complex join along the lines of: “RevenueDate + 1 Month = CalendarDate”

  • Create conformed dimension table “Comp Code”

  • Join both “Income” and “alias_Income_LastMonth” to “Comp Code” table

  • Setup two contexts (one for each fact table)

  • Create new universe measure “Last Month Income” based on “alias_Income_LastMonth” alias table

I hope this gives you enough ideas to play and tinker with.

Andreas :de: (BOB member since 2002-06-20)