BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Launch Works 
Launch Works (Opens a new window)  

General Notice: No events within the next 45 days.

current year vs last year variance (dynamic)

Goto page Previous  1, 2
 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
chris_c
Principal Member
Principal Member



Joined: 10 Jan 2006

Posts: 272
Location: Milwaukee WI


flag
PostPosted: Wed Oct 28, 2009 4:20 pm 
Post subject: Re: current year vs last year variance (dynamic)

This would be one of those situations where a Report Calculated Universe Object sure would come in handy. But that does not exist yet.

The only suggestion I can offer that that you'll have to add the LY column to your fact table to get the TY and LY in the same context.

_________________
Christopher Cammers
Core BTS
http://www.corebts.com
Back to top
Mark P
Forum Groupie
Forum Groupie



Joined: 03 Feb 2003

Posts: 8471
Location: Manchester, UK


flag
PostPosted: Wed Oct 28, 2009 4:21 pm 
Post subject: Re: current year vs last year variance (dynamic)

That said, if you want rolling 12 months versus rolling previous twelve months, it just makes your Last Year and This Year objects more complicated and you need to compare them to sales date.

It's all in the planning - build separate objects for what you need (including the @Prompt driven object) and put them together in the case statements.

_________________
Available from October 2014 for contract/consultancy work throughout the UK

BusinessObjects - versions 3, 4, 5, 6, XI, XIr2, XI3.1, 4.1, SAP Integration
Data Warehousing, SQL Server 2000/2005/2008 (inc. SSIS, DTS, linked servers, stored procs), Oracle 7.3-11g, Netezza, Sybase, Informix, DB2, Progress, Ingres
______________________________________
Democracy: Where two idiots outvote a genius
Back to top
selvaips
Principal Member
Principal Member



Joined: 03 May 2005

Posts: 309
Location: Chennai -India



PostPosted: Wed Oct 28, 2009 4:47 pm 
Post subject: Re: current year vs last year variance (dynamic)

Mark P wrote:

Please please please don't ask for any more code for this one. Everything you need is listed above. Engage your brain and apply what's there or this universe design business really isn't for you. Sorry to be blunt, but we provide help on here for free and the idea is to get you thinking for yourself, not us doing your job for you.



Mark,

The reason why people (or me) stuck up is because their environment/current design/database structure/universe should not changed because of the suggestions coming up in the forum. I cannot change whole of my universe just because am not able to make out. We think think think and think but it doesn't meant that we rely on you or asking you to do something for me. Either forum helps me or not, I need to make this happen in anyway.

Anyway, thanks for your reply.

_________________
Regards,
Selva
Back to top
selvaips
Principal Member
Principal Member



Joined: 03 May 2005

Posts: 309
Location: Chennai -India



PostPosted: Fri Oct 30, 2009 8:31 am 
Post subject: Re: current year vs last year variance (dynamic)

work around:

1. Create a variable using RowIndex() - This will give you unique id for each of the row in the report. If report returns 100 rows, then it will be 1 to 100 (If you have null values, use either RunningCount("A") - A would be some dummy variable)

2. Capture the UserResponse() of Top N

3. Sort the column which you want to Rank in desecending order

3. Add one more column in the report and use the formula like this - If useresponse() <= RowIndex() then 1 else 0. this will give you the value 1 or 0. In this case the first 5 rows will have value 1 and rest of the 95 rows will have 0 - Capture this in a variable.

4. Add a report filter and drag he variable and say =1. This will display the report which has value 1 always. This would return only first 5 rows.

_________________
Regards,
Selva
Back to top
Mark P
Forum Groupie
Forum Groupie



Joined: 03 Feb 2003

Posts: 8471
Location: Manchester, UK


flag
PostPosted: Fri Oct 30, 2009 8:45 am 
Post subject: Re: current year vs last year variance (dynamic)

selvaips wrote:
Mark P wrote:

Please please please don't ask for any more code for this one. Everything you need is listed above. Engage your brain and apply what's there or this universe design business really isn't for you. Sorry to be blunt, but we provide help on here for free and the idea is to get you thinking for yourself, not us doing your job for you.



Mark,

The reason why people (or me) stuck up is because their environment/current design/database structure/universe should not changed because of the suggestions coming up in the forum. I cannot change whole of my universe just because am not able to make out. We think think think and think but it doesn't meant that we rely on you or asking you to do something for me. Either forum helps me or not, I need to make this happen in anyway.

Anyway, thanks for your reply.


I think the message has lost a bit in translation but to be honest I prefer it if you guys learn rather than just ask for code.

This isn't personal. Learn how to write CASE statements, then if they don't work, ask. Nothing frustrates the guys answering questions on here more than people who haven't searched and/or tried the solution for themselves.

_________________
Available from October 2014 for contract/consultancy work throughout the UK

BusinessObjects - versions 3, 4, 5, 6, XI, XIr2, XI3.1, 4.1, SAP Integration
Data Warehousing, SQL Server 2000/2005/2008 (inc. SSIS, DTS, linked servers, stored procs), Oracle 7.3-11g, Netezza, Sybase, Informix, DB2, Progress, Ingres
______________________________________
Democracy: Where two idiots outvote a genius
Back to top
Mak 1
Forum Devotee
Forum Devotee



Joined: 06 Jan 2005

Posts: 11419
Location: London


flag
PostPosted: Fri Oct 30, 2009 9:03 am 
Post subject: Re: current year vs last year variance (dynamic)

Quote:
The reason why people (or me) stuck up is because their environment/current design/database structure/universe should not changed because of the suggestions coming up in the forum.


Any changes should be based on your requirement. All people have suggested here are the different ways that you can solve your problem.

The responses are very good to be honest, I would be more than pleased if I was asking the question icon_smile.gif.

Now, if I couldn't change the universe for the requirement I would work out where I needed to perform that piece of functionality.

Usually, I deal with variances on the report. If they cross contexts, I create the seperate queries and then build detail variables, against the conformed dimensions, to tie them together.

Quote:
but it doesn't meant that we rely on you or asking you to do something for me.


I don't think Mark said that, I think that he was saying that there was not a lot more he could contrbute to the post..

Thanks for updating the work around used for Top N....

_________________
Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 2 of 2 All times are GMT - 5 Hours
Goto page Previous  1, 2
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0132 seconds using 17 queries. (SQL 0.0024 Parse 0.0002 Other 0.0105)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo