topic Re: Prior Month Max Date in QlikView

Prior Month Max Date

Tue, 26 May 2015 11:53:22 GMT

Hi All

I have a Date field and would like to get the Max(Date) of the previous month.

Tried this expression but it doesn't work quiet nicely.

Date(Max(AddMonths(Date,-1)),'DD/MM/YYYY').

How can I get this?

Re: Prior Month Max Date

Joaquin_Lazaro — Tue, 26 May 2015 11:58:36 GMT

Hi tstewart

You have two functions MonthStart() and MonthEnd()

Date(MonthEnd(AddMonths(Date,-1)),'DD/MM/YYYY').

Hope this helps you

Joaquín

Re: Prior Month Max Date

john_s_w_wood — Tue, 26 May 2015 12:00:30 GMT

try monthstart(Date)-1

Re: Prior Month Max Date

jyothish8807 — Tue, 26 May 2015 12:01:16 GMT

Hi Tumelo,

Try like this:

Max(Date(AddMonths(Date,-1),'DD/MM/YYYY'))

Regards

Re: Prior Month Max Date

Tue, 26 May 2015 12:01:59 GMT

try this

=MonthsEnd(1,(date(AddMonths(today(),-1),'DD/MM/YYYY')))

Replace date field with today()

Re: Prior Month Max Date

Tue, 26 May 2015 12:02:11 GMT

Hi Joaquin

MonthEnd and MonthStart would not help me as I specifically want to consider the last date of each month in the dataset

Re: Prior Month Max Date

MayilVahanan — Tue, 26 May 2015 12:07:07 GMT

Hi Tumelo,

Try like this

=Date(AddMonths(Max(Date),-1),'DD/MM/YYYY')

Re: Prior Month Max Date

Tue, 26 May 2015 12:07:38 GMT

I want the expression to be dynamic e.g if last month's last sales day was 2015/04/28, This is the date I want to see from the expression. When I select any prior date, it should show me that date's previous month last date in the dataset

Re: Prior Month Max Date

stigchel — Tue, 26 May 2015 12:14:49 GMT

Try with

Max({<Date={"<$(=MonthStart(Max(Date)))"}>} Date)

Re: Prior Month Max Date

jyothish8807 — Tue, 26 May 2015 12:15:19 GMT

Did you tried my expression? It should work dynamically.

Regards

Re: Prior Month Max Date

Tue, 26 May 2015 12:19:25 GMT

Hi Jyothish

Yes I did, for some reason it doesn't show the correct dates

Re: Prior Month Max Date

Tue, 26 May 2015 12:21:20 GMT

It is not maxing the prior month date but shows me the same date as currently selected

Re: Prior Month Max Date

stigchel — Tue, 26 May 2015 12:38:39 GMT

That is because that expression is taking the max from (one month subtracted for each date) which is the current max e.g. May 26 and then one month subtracted so April26. If you want the max of the previous month use as suggested

Max({<Date={"<$(=MonthStart(Max(Date)))"}>} Date)

Re: Prior Month Max Date

Tue, 26 May 2015 12:44:32 GMT

Hi Piet

This expression doesn't return any results. can you please post an app where you are using it?

Re: Prior Month Max Date

stigchel — Tue, 26 May 2015 12:46:32 GMT

Sure, see text box

Re: Prior Month Max Date

Tue, 26 May 2015 12:51:33 GMT

Thanks Piet!