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

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: No events within the next 45 days.

[Webi 4.x] Previous months date


 
Search this topic... | Search Web Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Author Message
babu_272
Principal Member
Principal Member



Joined: 28 Jul 2010

Posts: 143


flag
PostPosted: Mon Jul 15, 2019 3:55 am 
Post subject: Previous months date

Hi ALl,

I have an tricky requirement.

I have 12 Measures and YTD Agg value where these 12 mesuares are from Jan Agg,Feb Agg and so on till Dec Agg and YTD agg is Year to date Aggregate where these roll ups are done at ETL level and I was given an Business Date feild which identifies data only when the data is Loaded and it doesn't maintain any historical data in it.

My Requirement is:

Now the month is July and I need to display data from jan,Feb,Mar etc till Jul and if Business Month is AUg then From Jan to Aug data to be displayed using YTD Agg measure for now Im doing in Manual way till Jul and rest of the months are 0s.

Could kindly anyone suggest me or guide me how to do this?

The data should be Dynamic rather than Hard coded
Back to top
Maddye
Principal Member
Principal Member



Joined: 09 Jan 2009

Posts: 158
Location: Cambridgeshire


flag
PostPosted: Mon Jul 15, 2019 7:11 am 
Post subject: Re: Previous months date

I do a similar sort of activity report but I'm interested in month-on-month activity plus a cumulative YtD figure. I used a prompt report with 2 date prompts: Period Start & Period End. Period Start is always the first date in the financial year: 1st April. Period End will be each month in turn until the year ends the following 31st March.

From the prompted dates I got

Period Start = todate(userresponse("Prompt value Period Start")
Period End = todate(userresponse("Prompt value Period End")
Year of start date = year(Period Start)
Month of start date = month number of year(Period Start)

I then created a set of interim dates within the report (we don't have access to the universe):

Month 01 Start = Period Start (based on UserResponse)
Month 01 End = lastdayofmonth(Month 01 Start)
Month 02 Start = turn the concatenated value of "01/"+(Month of start date + x)+"/"+Year
Month 02 End = lastdayofmonth(Month 02 Start)
and so on

Note that you'll need to format numbers, especially the year, to get rid of the comma.

You'll also need to work out whether you'll need to use Year or Year +1 depending on whether you're working with a calendar year or not.

It did get a bit involved in that if the cumulative month number was >12 you need to subtract 12 to get the correct month number.

Obviously, the YtD figure is based on activity from Period Start to Period End.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
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.0270 seconds using 18 queries. (SQL 0.0029 Parse 0.0008 Other 0.0233)
CCBot/2.0 (https://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