Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Last Full Month

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

25 Replies
swuehl
MVP
MVP

I assume you would consider Today =  Jun 30 to indicate a full month?

Try

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


Take care that the format of your Bill Date field values match the format of your dollar sign expanded values.

Anonymous
Not applicable
Author

try to put num around Expression

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

Not applicable
Author

this shows nothing again

varshavig12
Specialist
Specialist

Try this:

sum({<[Bill Date]={"<$(=MonthStart(today())) >=$(=MonthStart(addmonths(today(),-1))) "}, Month=  >} [Bill Value] )

varshavig12
Specialist
Specialist

Try this for DD/MM/YYY format:

sum({<[Bill Date]={"<$(=date(MonthStart(today()),'DD/MM/YYYY') >=$(=date(MonthStart(addmonths(today(),-1)),'DD/MM/YYYY') "}, Month= , Year= >} [Bill Value] )

This should give you:

sum({  [Bill Value]={  < '06/01/2016'  and >= '05/01/2016'  }  } [Bill Value] )

Note: instead of today() you can use max(Date_Field)

Not applicable
Author

Fantastic it works, thank you so much.

if I wanted the formula to return Aprils data instead of May. What would I need to change?