topic Re: Date range in set analysis in QlikView

Date range in set analysis

Wed, 10 Jun 2015 13:14:15 GMT

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

Re: Date range in set analysis

avinashelite — Wed, 10 Jun 2015 13:17:50 GMT

try something like this :

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

Re: Date range in set analysis

sunny_talwar — Wed, 10 Jun 2015 13:17:59 GMT

Try this:

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

Re: Date range in set analysis

Gysbert_Wassenaar — Wed, 10 Jun 2015 13:18:07 GMT

Try:

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

Re: Date range in set analysis

fvelascog72 — Wed, 10 Jun 2015 13:18:59 GMT

Hi,

You can use MonthEnd(Today())

Re: Date range in set analysis

avinashelite — Wed, 10 Jun 2015 13:19:27 GMT

your final expression would be something like this:

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

Re: Date range in set analysis

giakoum — Wed, 10 Jun 2015 13:23:39 GMT

you are missing the $ sign.

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

Re: Date range in set analysis

Wed, 10 Jun 2015 13:24:46 GMT

That worked perfectly, thank you sunindia

Re: Date range in set analysis

sunny_talwar — Wed, 10 Jun 2015 13:26:26 GMT

Awesome