topic YTD addmonth query in QlikView

YTD addmonth query

Wed, 02 Jun 2010 15:12:20 GMT

Hi There, i am so close to solving this, please can someone help me with this expression, i have a simple dashboard where i need to compare 2 years Year To Date, it is a count of members this time last year:

Count ({< TransactionDate={">=$(=addmonths(today(),-12))"}>} distinct MemberNo)

TransactionDate = YYYYMMDD

It is bringing back all the values for last year instead of only this time last year.

Thanks

YTD addmonth query

johnw — Wed, 02 Jun 2010 21:06:49 GMT

For YTD, don't you want "<=" 12 months ago? And you might need to format the result for comparison. And you'll need to give it a starting date of the first of that year as well. So my first guess is this:

count({<TransactionDate={">=$(=date(yearstart(today(),-1),'YYYYMMDD')) <=$(=date(addmonths(today(),-12),'YYYYMMDD'))"}>} distinct MemberNo)

YTD addmonth query

Thu, 03 Jun 2010 08:29:30 GMT

Hi John, Thanks, that worked perfectly, for some reason i had to change the -12 to -13 to get the exact YTD comparison for last year which gives me exactly what i need. Much appreciated.