topic Re: Current year and previous year in QlikView

Current year and previous year

gauthamchilled — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Current year and previous year

sunny_talwar — Fri, 29 Jul 2016 05:28:39 GMT

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

Re: Current year and previous year

gauthamchilled — Fri, 29 Jul 2016 06:11:45 GMT

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

Re: Current year and previous year

sunny_talwar — Fri, 29 Jul 2016 06:15:21 GMT

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)

MaxString(Period)

MaxString(Period, 3)

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

Re: Current year and previous year

phaneendra_kunc — Fri, 29 Jul 2016 06:18:59 GMT

Lets say if you have selected FY2014 then try this

Current Year = maxstring(Period)

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

Re: Current year and previous year

sunny_talwar — Fri, 29 Jul 2016 06:21:56 GMT

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

FirstSortedValue(Period, -PeriodActual)

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

MaxString(Period)

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

Re: Current year and previous year

prashantbaste — Fri, 29 Jul 2016 06:51:40 GMT

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

Re: Current year and previous year

gauthamchilled — Fri, 29 Jul 2016 06:52:42 GMT

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

thanks.

Re: Current year and previous year

sunny_talwar — Fri, 29 Jul 2016 06:57:44 GMT

Modified phaneendra.kunche‌ solution for previous year:

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

Re: Current year and previous year

sunny_talwar — Fri, 29 Jul 2016 06:59:08 GMT

And also modified my expression:

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

Re: Current year and previous year

gauthamchilled — Fri, 29 Jul 2016 08:47:53 GMT

hi sunny,

one more help attached the same file with a chart, what i want is

if i select year fy2015, the chart should show sales for fy2015,fy2014,fy2013

if i select year my2015, the chart should show sales for my2015,my2014,my2013

how can i do this? thanks in advance

Re: Current year and previous year

sunny_talwar — Fri, 29 Jul 2016 08:50:48 GMT

Checking now

Re: Current year and previous year

sunny_talwar — Fri, 29 Jul 2016 08:52:08 GMT

Try this expression:

Sum({<Period = {"$(=Left(Period, 2) & '*')"}>}sales)