topic Re: Max Month minus 1 in QlikView

Max Month minus 1

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

Hi,

I have the expression - Count({<ADMIT_MNTH = {"$(=Max(ADMIT_MNTH))"}>}[Patient]) which gives me the max month but i know need the same expression but the previous month so MINUS 1

Thanks

Kev

Re: Max Month minus 1

dplr-rn — Mon, 10 Aug 2020 14:25:50 GMT

use addmonths function with -1

Count({<ADMIT_MNTH = {"$(=AddMonths(Max(ADMIT_MNTH),-1))"}>}[Patient])

Re: Max Month minus 1

kev6brown — Mon, 10 Aug 2020 14:31:09 GMT

Comes through as zeros

Re: Max Month minus 1

dplr-rn — Mon, 10 Aug 2020 14:41:08 GMT

check output of this expression in a text object and check

AddMonths(Max(ADMIT_MNTH),-1)

Re: Max Month minus 1

Kushal_Chawda — Mon, 10 Aug 2020 14:42:34 GMT

try below

assuming ADMIT_MNTH format is MMM YYYY. In below set you can try giving actual format

Count({<ADMIT_MNTH = {"$(=date(addmonths(Max(ADMIT_MNTH),-1),'MMM YYYY'))"}>}[Patient])

Re: Max Month minus 1

kev6brown — Mon, 10 Aug 2020 14:45:09 GMT

That gives me the previous month but in the format 01/06/2020 maybe that's why

Re: Max Month minus 1

dplr-rn — Mon, 10 Aug 2020 14:50:10 GMT

@kev6brown wrap it with date and try

Re: Max Month minus 1

Ke_Sg — Thu, 05 Dec 2024 15:15:02 GMT

Hi,

I know this is an old thread (well, this thread is the first on Google 😃), but I had a kinda-similar problem, when I had to lookup the value before highest value.

If you use

max([Field])

you obviously get highest value of [Field]. With this value, you can do simple maths, like max()-1 or "add" -1 day, month ... to a date. But if the value before the highest is not guessable, you can use the second parameter of max() function :

max([Field], 2)

This will return the value before the highest, in both Qlikview and Qliksense. A similar parameter also exists for min().

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/ChartFunctions/BasicAggregationFunctions/max.htm

In that case, this should do the trick :

Count({<ADMIT_MNTH = {"$(=Max(ADMIT_MNTH, 2))"}>}[Patient])