Date range in set analysis

[#1]

Hi, hope all you clever guys out there could help with a piece of set analysis I'm struggling with

I'm trying to set a date range that looks from the beginning of this year to the last day of last month. This works fine, if I fix the dates as below.

=sum({<UNIT.SOLD_DATE= {">=01/01/2015 <=31/05/2015"} >} UNIT.TOTALSOLD)

But if I want to get Qlikview to recognise the last day of the last month automatically I did this but it doesn't work.

=sum({<UNIT.SOLD_DATE= {">=01/01/2015 <=(=date(today())-(day(today())"} >} UNIT.TOTALSOLD)

Thanks for any help, Simon

[avinashelite]

try something like this :

=MonthEnd(MonthName(today())-1)

[sunny_talwar · Accepted solution]

Try this:

=Sum({<UNIT.SOLD_DATE = {">=01/01/2015<=$(=Date(Date(Today())-Day(Today()), 'DD/MM/YYYY'))"}>} UNIT.TOTALSOLD)

[Gysbert_Wassenaar]

Try:

=sum({<UNIT.SOLD_DATE= {">=01/01/2015<=$(=date(monthend(today(),-1),'DD/MM/YYYY'))"} >} UNIT.TOTALSOLD)

[fvelascog72]

Hi,

You can use MonthEnd(Today())

[avinashelite]

your final expression would be something like this:

=sum({<UNIT.SOLD_DATE= {">=01/01/2015 <=(=MonthEnd(MonthName(today())-1))"} >} UNIT.TOTALSOLD)

[giakoum]

you are missing the $ sign.

Gysbert's expression is safer as it also includes the format.

[#8]

That worked perfectly, thank you sunindia

[sunny_talwar]

Awesome