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.

Creating Custom Ranges


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



Joined: 03 Dec 2018

Posts: 1



PostPosted: Mon Dec 03, 2018 4:38 pm 
Post subject: Creating Custom Ranges

I'm trying to create custom year ranges that span from 09/01-08/31. So for example, I want 09/01/2018-08/31/2019 to be 2018-2019 and 09/01/2017-08/31/2018 to be 2017-2018.

My formula looks like this:

Code:
=If(FormatDate([Academic Year View].[Job Effdt];"MM/dd/yyyy")>="09/01/2018" And FormatDate([Academic Year View].[Job Effdt];"MM/dd/yyyy")<="08/31/2019";"2018-2019";0)


This results in 0 for all dates regardless of whether they are in that range or not.

I tried to make it simpler by using
Code:
=If(FormatDate([Academic Year View].[Job Effdt];"MM/dd/yyyy")>="09/01/2018";1;0)


This returned 0's for all dates between 09/01 and 12/31 and seemed to ignore the year completely.

Any help is greatly appreciated.
Back to top
buzz
Principal Member
Principal Member



Joined: 11 Aug 2005

Posts: 170
Location: Sydney


flag
PostPosted: Mon Dec 03, 2018 10:26 pm 
Post subject: Re: Creating Custom Ranges

To make it work for all years use the following

Code:
=If(MonthNumberOfYear([MyDate]) < 9)
Then FormatNumber(Year([MyDate]) - 1;"0000") + "-" + FormatNumber(Year([MyDate]);"0000")
Else FormatNumber(Year([MyDate]);"0000") + "-" + FormatNumber(Year([MyDate]) + 1;"0000")
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Desktop 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.0329 seconds using 17 queries. (SQL 0.0027 Parse 0.0009 Other 0.0293)
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