topic Use Yesterday in expression in QlikView

Use Yesterday in expression

gerhardl — Fri, 31 Aug 2012 10:24:29 GMT

Hi,

please help me with this expression - cannot get it right:

Count({<[Posted Date]={"=date(today()-1)"}>} [Trans Amount])

I want to count the number of transactions that have a Posted Date of Yesterday.

Thanks in advance,

Gerhard

Re: Use Yesterday in expression

tresB — Fri, 31 Aug 2012 10:56:41 GMT

Hello,

http://community.qlik.com/message/20182#20182

Regards,

tresesco

Re: Use Yesterday in expression

gerhardl — Fri, 31 Aug 2012 11:45:34 GMT

Thanks Tresesco,

but that's still not working. I now have:

Count({<[Posted Date]={"=$(=date(today()-1,'YYYY/MM/DD'))"}>} [Trans Amount])

But it gives me the same value as Count([Trans Amount])

Re: Use Yesterday in expression

tresB — Fri, 31 Aug 2012 12:45:46 GMT

you always have a scope of checking if the date(today()-1,'YYYY/MM/DD') producing the right date in right format. i guess the issue is with date format.

Re: Use Yesterday in expression

Fri, 31 Aug 2012 12:46:46 GMT

what is your date format?

Re: Use Yesterday in expression

gerhardl — Fri, 31 Aug 2012 12:50:01 GMT

SET DateFormat='YYYY/MM/DD';

Load

DATE(DATE#( left([Posted Date],11), 'DD-MMM-YY')) as [Posted Date],

Re: Use Yesterday in expression

Fri, 31 Aug 2012 12:53:02 GMT

could you send small sample qvw file ?

Re: Use Yesterday in expression

tresB — Fri, 31 Aug 2012 13:01:21 GMT

Did you try - Count({<[Posted Date]={"=$(=date(today()-1,'DD-MMM-YY'))"}>} [Trans Amount]) ?

Re: Use Yesterday in expression

gerhardl — Fri, 31 Aug 2012 13:03:03 GMT

Yes, that give me zero values.

Re: Use Yesterday in expression

gerhardl — Fri, 31 Aug 2012 13:07:22 GMT

Please see attached.

Re: Use Yesterday in expression

tresB — Fri, 31 Aug 2012 13:16:35 GMT

use date format as 'YYYY/MM/DD'... it would work.

Count({<[Posted Date]={"=$(=date(today()-1,'YYYY/MM/DD'))"}>} [Trans Amount])

See attachment

Re: Use Yesterday in expression

gerhardl — Fri, 31 Aug 2012 13:35:45 GMT

I have QV Personal Edition so can't open your file - but I literally copy and paste that expression as it is and use it in the file I just uploaded and it does not work.

It returns a count of 4 payments received, when only 2 were received on 2012/08/30.

Re: Use Yesterday in expression

Fri, 31 Aug 2012 13:37:42 GMT

Count({<[Posted Date]={"$(=date(vYESTERDAY))"}>} [Trans Amount])

you use [Posted date] instead [Posted Date]

Re: Use Yesterday in expression

gerhardl — Fri, 31 Aug 2012 13:43:11 GMT

Yes, thanks!

That's why I added that variable - then I made that stupid mistake. It works now.

Still don't understand why

Count({<[Posted Date]={"=$(=date(today()-1,'YYYY/MM/DD'))"}>} [Trans Amount])

doesn't work - but not matter.