topic Re: Max month minus 1 in QlikView

Max month minus 1

rcorcoran — Tue, 27 Sep 2016 08:51:19 GMT

Hi The expression below is giving me the max YearMonth but I cannot get it to work for the Max YearMonth -1

Any help appreciated

if(isnull(num((((Avg({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth))"}>}ActualValue)))), vNumber)),Null(),

num((((sum({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth))"}>}ActualValue)))), vNumber))

Re: Max month minus 1

giakoum — Tue, 27 Sep 2016 08:54:30 GMT

What is the YearMonth format? 201606 for example?

Re: Max month minus 1

rcorcoran — Tue, 27 Sep 2016 08:59:04 GMT

it is made by the text below in the script and is YYYY,MM

MonthName(Date(makedate(ReferredYYYY,ReferredMM))) as YearMonth,

Re: Max month minus 1

Anil_Babu_Samineni — Tue, 27 Sep 2016 09:00:47 GMT

I recommend you to create Variable for that

For prev month you can try this too

if(isnull(num((((Avg({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth)-1)"}>}ActualValue)))), vNumber)),Null(),

num((((sum({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth)-1)"}>}ActualValue)))), vNumber))

if(isnull(num((((Avg({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Addmonths(Max({<[HRPrimaryCare]={7}>}YearMonth)),-1)"}>}ActualValue)))), vNumber)),Null(),

num((((sum({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Addmonths(Max({<[HRPrimaryCare]={7}>}YearMonth)),-1)"}>}ActualValue)))), vNumber))

Note: AddMonth(Expression, -1) will gives the previous month Data. If not, better to offer you logic if you shared the application

Re: Max month minus 1

varshavig12 — Tue, 27 Sep 2016 09:01:20 GMT

addmonths

Re: Max month minus 1

sunny_talwar — Tue, 27 Sep 2016 09:02:38 GMT

How about this:

Avg({$<HRPrimaryCare = {7}, ReferredYYYY, YearMonth = {"$(=MonthName(AddMonths(Max({<[HRPrimaryCare] = {7}>} YearMonth), -1)))"}>} ActualValue)

and for this month, you might be able to simplify your expression to this

Avg({$<HRPrimaryCare = {7}, ReferredYYYY, YearMonth = {"$(=MonthName(AddMonths(Max({<[HRPrimaryCare] = {7}>} YearMonth), 0)))"}>} ActualValue)