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

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: No events within the next 45 days.

[Webi 4.x] Returning 'Latest Season' Code


 
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
JamieA
Forum Member
Forum Member



Joined: 13 Feb 2018

Posts: 2



PostPosted: Tue Feb 13, 2018 4:32 am 
Post subject: Returning 'Latest Season' Code

Hi,

I'm trying to build what should be a simple report which returns the 'Latest Season' in which a SKU/product appeared. The SKU-specific season codes are in a SPRING17, AUTUMN17, etc. format so simply using =MAX([Latest Season]) when reporting at product level returns SPRING17 rather than AUTUMN17, in this instance.

Any ideas would be appreciated..

Thanks,
Jamie.
Back to top
ABILtd
Forum Enthusiast
Forum Enthusiast



Joined: 08 Feb 2006
ASUG Icon
Posts: 1819


flag
PostPosted: Tue Feb 13, 2018 6:03 am 
Post subject: Re: Returning 'Latest Season' Code

Yeah, it`s done the max based on the alpha and the result is actually correct. Webi doesn`t know the inferred numerical sequence of seasons.....

Wondering if you actually write an if then else to order the seasons dimension, and then try the max on the result?

_________________
BI and Analytics Presales Consultant
------------------------------------------------------
BOXI R1, R2, R3, R3.1, R4.1, R4.2, CE 8, 8.5, 9, 10, SAP Analytics Cloud, Crystal Reports, Lumira, PowerBI, Tableau, JasperServer, iReport, LogiAnalytics, BIRST, Qlikview, Xcelcius, Netezza, T-SQL, PLSQL, DTS, SSIS, BODI, BODS, Kimble etc....
------------------------------------------------------
Back to top
tendernips
Principal Member
Principal Member



Joined: 27 Jul 2017

Posts: 105



PostPosted: Tue Feb 13, 2018 9:38 am 
Post subject: Re: Returning 'Latest Season' Code

If you have a dedicated year object you could use that to set the year then max assigned to spring through fall manually like ABILtd suggested.

If you don't have a dedicated year object, you could take out the year and force it to be a string:
Year:
=FormatNumber(ToNumber(right([latest season];2);"0");"0")
from that latest season to get at the two digit year.

Then make your season manually with if/then and and append it to the year string.
Season:
=Left([Latest Season];Length([Latest Season])-2)

Year Season:
=[Year] + If [Season] = "SPRING" Then 1 Elseif [Season] = "AUTUMN" Then 2 Etc. for however many you need to consider in whatever order.

You want the max calculation to consider year first, then look at the season next. yys where s = season is mimicking yyyyq date formatting which should meet your requirements to work in a max variable. So 172 > 161 and 181 >172
Back to top
JamieA
Forum Member
Forum Member



Joined: 13 Feb 2018

Posts: 2



PostPosted: Wed Feb 14, 2018 3:51 am 
Post subject: Re: Returning 'Latest Season' Code

Fantastic! Thanks guys.

@tendernips - your solution worked perfectly using =Max([Year Season] ForEach ([Product];[SKU Code]))

Thanks again!

Best wishes,
Jamie.
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.0338 seconds using 17 queries. (SQL 0.0029 Parse 0.0009 Other 0.0299)
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