topic Re: Last Full Month in QlikView

Last Full Month

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to run a report for last full month regardless of the date

For example if I run the report now I would like to retrieve May's data. It also needs to be dynamic.

Any help would be much appreciated

Re: Last Full Month

ciaran_mcgowan — Mon, 20 Jun 2016 13:23:46 GMT

Hi Dave,

Can you post a copy of your report as it exists now. Usually you would use AddMonths function to achieve this but if I can see what you're trying to accomplish, I might be able to help more.

AddMonths(Max(YourDate),-1)

Re: Last Full Month

Anonymous — Mon, 20 Jun 2016 13:32:57 GMT

if you Need to run your Report with last months data you have two Options depending on your datamodel

1) you have field which contains full date Format (day, month, year)

then you Need to run your Report with

(yourdatefield >= monthstart(addmonths(today(),-1)) and (yourdatefield <= monthend(addmonths(today,-1))

2) if you have a month field and year field

(youryearfield = year(addmonths(today(),-1)) and (youmonthfield = month(addmonths(today(),-1))

Re: Last Full Month

Anonymous — Mon, 20 Jun 2016 14:13:41 GMT

Hi Dave,

Make a variable with this code:

If(YourDate = MonthEnd(YourDate),YourMonth,Month(Addmonths(YourDate),-1))

With this code you will test if current month is complete, if it is true you will get current month, else, you will get last complete month.

Select your report with this var.

Regards!!

Re: Last Full Month

juleshartley — Mon, 20 Jun 2016 14:56:35 GMT

you might need a 'floor' in this to be sure no time element causing issues...

if(YourDate=Floor(MonthEnd(YourDate)), Floor(MonthEnd(YourDate)), Floor(MonthEnd(AddMonths(YourDate,-1))))

Re: Last Full Month

Anonymous — Mon, 20 Jun 2016 15:17:34 GMT

If the field is date format, and not is TimeStamp format, you don't need to use Floor function.

Regards.

Re: Last Full Month

Mon, 20 Jun 2016 16:11:51 GMT

this errors after the and ay ideas?

Re: Last Full Month

Anonymous — Mon, 20 Jun 2016 19:46:26 GMT

can you post sample?

Re: Last Full Month

susovan — Tue, 21 Jun 2016 05:27:04 GMT

Hi Dave,

Try this expression,

sum({<Date={">=$(vMonthStart)<=$(vMonthEnd)"},Month=,Quarter=,FullYear=>} Amount)

Here

vMonthStart=Date(MonthStart(Max(@_Date)),'DD/MM/YYYY')

vMonthEnd=Date(Max(@_Date),'DD/MM/YYYY')

Re: Last Full Month

Tue, 21 Jun 2016 08:58:03 GMT

Sum({<[Bill Date] {">= monthstart(addmonths(today(),-1)) and [Bill Date] <= monthend(addmonths(today(),-1))"} > [Bill Value])

I am trying to sum bill value by bill date

Re: Last Full Month

Tue, 21 Jun 2016 08:59:16 GMT

Hi Susovan

Thanks for your reply. I don't want to add variables as the report needs to be dynamic

Re: Last Full Month

Anonymous — Tue, 21 Jun 2016 09:12:58 GMT

Syntax is wrong (not sure if my Syntax is correct - could not test it)

Sum({<[Bill Date]={">= $(=monthstart(addmonths(today(),-1)))<=$(=monthend(addmonths(today(),-1)))"} >} [Bill Value])

Re: Last Full Month

susovan — Tue, 21 Jun 2016 09:19:25 GMT

So, you can try this

sum({<Date={">=$(=Date(MonthStart(Max(@_Date)),'DD/MM/YYYY'))<=$(=Date(Max(@_Date),'DD/MM/YYYY'))"},Month=,Quarter=,FullYear=>} Amount)

Re: Last Full Month

Tue, 21 Jun 2016 09:46:42 GMT

thought it was the syntax. This display data going back to March, what do I need to do to change it to May only

Re: Last Full Month

Anonymous — Tue, 21 Jun 2016 11:45:54 GMT

try this (to get only may data)

Sum({<[Bill Date]={">= 01.05.2016<=31.05.2016"} >} [Bill Value])

Re: Last Full Month

Tue, 21 Jun 2016 15:04:02 GMT

is there a more dynamic way to do it?

Re: Last Full Month

Anonymous — Wed, 22 Jun 2016 06:34:23 GMT

can you specify more in Detail your requirements?

if you have the data in a variable for instance

Sum({<[Bill Date]={">= $(=monthstart(variablemonthdate))<=$(=monthend(variablemonthdate))"} >} [Bill Value])

or if you have two variable for start and end date

Sum({<[Bill Date]={">= $(=variablemonthstart)<=$(=variablemonthend)"} >} [Bill Value])

please check that variable and field have same dateformat

Re: Last Full Month

Thu, 23 Jun 2016 14:17:06 GMT

Rduolf - I'm nearly I think. I've put the formula in as detailed above

Sum({<[Bill Date]={">= $(=monthstart(addmonths(today(),-1)) <=$(=monthend(addmonths(today(),-1)))"} >} [Bill Value])

Unfortuante;y I'm getting data from March to the end of May any idea why. I only want the data from the last full month

Re: Last Full Month

Anonymous — Fri, 24 Jun 2016 07:50:09 GMT

please try this Expression in a TextBox

=monthstart(addmonths(today(),-1)) & '-' & max([Bill Date])

does both dates are from same Format?

does first Expression Shows 1.05.2016?

Re: Last Full Month

Fri, 24 Jun 2016 08:36:29 GMT

the expression shows 01/05/2016-42544......not sure why as the date shows correctly in the table