topic Re: MTD yesterday in QlikView

MTD yesterday

stabben23 — Fri, 01 Feb 2013 08:56:10 GMT

I found a problem today when a new month begin.

I show MTD, but it will always calculate yesterdays value, so MTD should today show 1/1-31/1.

My set expression is : sum({$<Date ={">=$(=$(vThisMonthsStart))<=$(=$(vYesterday))"}>}Price)

This expression will works fine tomorrow (2/2) but not today (1/2), and i know that vThisMonthsStart is the problem.

vThisMonthStart = date(date(MonthStart(today()))'YYYYMMDD') and this will be 20130201 today, NOT good.

date(date(MonthStart(today()-1))'YYYYMMDD') will give me the correct 20130101, but what happens tomorrow with that variable?

Will it still be 20130101?

This will also give me a second problem that is how to find vThisMonthsStartLastYear.

Re: MTD yesterday

Gysbert_Wassenaar — Fri, 01 Feb 2013 10:47:17 GMT

Tomorrow, today being the 1st of februari, date(MonthStart(today()-1))'YYYYMMDD') will return 20130201. So it looks like that would be the correct expression for your purposes.

edit: vThisMonthsStartLastYear can be calculated with addyears($(vThisMonthStart),-1) assuming you want that based on yesterday too.

Re: MTD yesterday

stabben23 — Fri, 01 Feb 2013 10:51:26 GMT

I'll try and see whats happens tomorrow. Any clue how i shall solv vThisMonthsStartLastYear?

This problem will only show up 1:st every month, but its not ok anyway.