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

[JamieA]

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.

[ABILtd]

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?

[tendernips]

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

[JamieA]

Fantastic! Thanks guys.

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

Thanks again!

Best wishes,
Jamie.