topic Re: how to freeze max Year-month data in QlikView

how to freeze max Year-month data

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

Hi Experts,

I know this is simple, but I'm not getting Max-Year Month data..

My Expression like

=Sum({<Year={'$(=Max(Year))'}, MonthNum={'$(=Max(MonthNum))'}>}Sales)

Here, taking by default MaxYear=2011 and Max Month = Dec (Dec not correct, my max year month April)

that should be year=2011 and Month = April..

Thanks in Advance...!

ashwin_r — Fri, 22 Jun 2018 04:57:45 GMT

Try this,

=Sum({<Year={'$(=Max(Year))'}, MonthNum={'$(=Max({<Year={'$(=Max(Year))'}>}MonthNum))'}>}Sales)

qlikviewwizard — Fri, 22 Jun 2018 05:05:14 GMT

Hi,

Please check this solution.

Binary maxyearmonth.qvw;

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

NoConcatenate

MasterCalendar_Final:

LOAD DATE(Date#(Monthstart(OrderDate),'MM/DD/YYYY'),'MMM-YY') as MonthYear,*

Resident MasterCalendar;

DROP TABLE MasterCalendar;

DROP Field YearMonth1 from MasterCalendar_Final;

Read this for more information: A Primer on Set Analysis

qlikviewwizard — Fri, 22 Jun 2018 05:06:41 GMT

Expressions:

Max YearMonth:

Sum({<MonthYear={'$(=Max(MonthYear))'}>} LineSalesAmount)

Prev_Max YearMonth:

Sum( {$<MonthYear={"$(=Date(AddMonths(Max(MonthYear),-1),'MMM-YY'))"}>} LineSalesAmount )

sunny_talwar — Fri, 22 Jun 2018 11:34:46 GMT

Slight modification...

=Sum({<Year = {"$(=Max(Year))"}, MonthNum = {"$(=Max({<Year = {[$(=Max(Year))]}>} MonthNum))"}>} LineSalesAmount)