topic Re: Get Max Period for Year in QlikView

Solved

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

Solved

Re: Get Max Period for Year

sunny_talwar — Mon, 18 Apr 2016 18:10:50 GMT

May be this:

Table:

LOAD [Fiscal Year],

Max(Month_Year_Calendar) as Month_Year_Calendar,

FirstSortedValue(Quantity, -Month_Year_Calendar) as Quantity

FROM

[https://community.qlik.com/thread/213623]

(html, codepage is 1252, embedded labels, table is @1)

Group By [Fiscal Year];

Re: Get Max Period for Year

swuehl — Mon, 18 Apr 2016 18:13:34 GMT

If your Month_Year_Calendar field is a calendar field with numeric representation (and assuming a chart solution);

Chart with dimension Fiscal Year and two expressions:

=Date(Max(Month_Year_Calendar),'MMM-YY')

=FirstSortedValue(Quantitiy, -Month_Year_Calendar)

[Note that there is a discrepancy between your Fiscal year values and the year in Month_Year_Calendar?]

Re: Get Max Period for Year

sunny_talwar — Mon, 18 Apr 2016 18:14:37 GMT

For your sample:

Table:

LOAD [Fiscal Year],

Date(Max(Date#(Month_Year_Calendar, 'MMM-YY')), 'MMM-YY') as Month_Year_Calendar,

FirstSortedValue(Quantity, -Date#(Month_Year_Calendar, 'MMM-YY')) as Quantity

FROM

[https://community.qlik.com/thread/213623]

(html, codepage is 1252, embedded labels, table is @1)

Group By [Fiscal Year];

Re: Get Max Period for Year

Peter_Cammaert — Mon, 18 Apr 2016 18:16:03 GMT

Maybe tune this a bit into:

MaxTable:

LOAD [Fiscal Year],

Max(Month_Year_Calendar) AS MaxCalendarMonth,

FirstSortedValue(Quantity, -Month_Year_Calendar) AS MaxCalendarMonthQuantity

RESIDENT YourInputTable

GROUP BY [Fiscal Year];

Best,

Peter

Re: Get Max Period for Year

Peter_Cammaert — Mon, 18 Apr 2016 18:18:30 GMT

Fiscal Year is most often the year of the last calendar period in the fiscal year.

Fiscal years can vary in length (now its 18 periods, after it's just 10 etc.)

Fiscal years don't have to align with calendar years.

Or what discrepancy do you mean?

Peter

Re: Get Max Period for Year

swuehl — Mon, 18 Apr 2016 18:23:25 GMT

Right, needs to get my eyes checked, I've misread some values in the second column (or need a bigger screen).