
Author |
Message |
JamieA Forum Member


Joined: 13 Feb 2018
 Posts: 2

|
Posted: 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


Joined: 08 Feb 2006
              Posts: 1836

|
Posted: 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


Joined: 27 Jul 2017
 Posts: 105

|
Posted: 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


Joined: 13 Feb 2018
 Posts: 2

|
Posted: 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 |
|
 |
|
|