Current year and previous year

[gauthamchilled]

hi

i have year field something like this FY stands for full year and MY stands for Mid year.

Period, PeriodActual

FY2015,20152

MY2015,20151

FY2014,20142

MY2014,20141

FY2013,20132

MY2013,20131

To get current year, i use =Max({<Period=>} PeriodActual), but it gives me 20152 not period.

i want current year and previous year calculation to show period

current year=fy2015 and previous year fy2014

help plz

[sunny_talwar]

You can use this:

FirstSortedValue({<Period>} Period, -PeriodActual)

FirstSortedValue({<Period>} Period, -PeriodActual, 3)

or you can do something like this

LOAD Dual(Period, PeriodActual) as Period,

          PeriodActual

FROM....

And then may be this:

MaxString({<Period>} Period) to get FY2015

MaxString({<Period>} Period, 3) to get FY2014

[gauthamchilled]

Hi Sunny.

Thanks for the response. It works but however when i select any of the other year in the field, current year and previous year not changing based on the current selection.

I want current selected year as current year, and previous year to be -1 according to the current selection.

if my current selection is fy2014, current year  to be fy2014 and previous year to be fy2013

[sunny_talwar]

Not sure which method you took, but I guess I carried over the logic to ignore selection in Period field. If you don't want it, remove it

FirstSortedValue(Period, -PeriodActual)

FirstSortedValue(Period, -PeriodActual, 3)

or

MaxString(Period)

MaxString(Period, 3)

If you notice, I removed {<Period>} from the above expressions

[phaneendra_kunc]

Lets say if you have selected FY2014 then try this

Current Year = maxstring(Period)

Prior Year = 'FY'&(num(right(maxstring(Period),4))-1)

[sunny_talwar]

I guess I understand the concern now, may be this will also work

FirstSortedValue(Period, -PeriodActual)

FirstSortedValue({<PeriodActual = {"$(='<=' & Max(PeriodActual))"}, Period>}Period, -PeriodActual, 3)

or

MaxString(Period)

MaxString({<PeriodActual = {"$(='<=' & Max(PeriodActual))"}, Period>} Period, 3)

[prashantbaste]

Hello Gautham Prasad,

You can try expressions as -

For Current Year (FY2015) :  Only( {<PeriodActual={'$(=Max(PeriodActual))'}>} Period)

For Previous Year (FY2014) :  'FY'&(Right(Only( {<PeriodActual={'$(=Max(PeriodActual))'}>} Period),4)-1)

Values will be changes based on selections made.

Hope this will help to resolve issues.

--

Regards,

Prashant P Baste

[gauthamchilled]

Thanks sunny and phanneendra for the help...still not getting it correct solution...attached the file with explanation..appreciating your help.

thanks.

[sunny_talwar]

Modified phaneendra.kunche‌ solution for previous year:

=Left(Period, 2) & (num(right(maxstring(Period),4))-1)

[sunny_talwar · Accepted solution]

And also modified my expression:

=FirstSortedValue({<PeriodActual = {"$(='<=' & Max(PeriodActual))"}, Period>} Period, -PeriodActual, 3)