topic Re: YTD last month in QlikView

YTD last month

Thu, 05 Jun 2014 09:53:31 GMT

I've used this post to get Rolling 1 and 12 months calculation correct.

http://community.qlik.com/message/243408

Now I want to use the same technique to calculate last month YTD

Since I will load the data after turn of the month.

Below is the string I need to modify so that "-13" becomes more of a variable is there a way to use count?

Sum({<MonthYear ={">=$(=Date(addmonths(Max(MonthYear), -13), 'MMM-YY')) <=$(=Date(addmonths(Max(MonthYear), -1), 'MMM-YY'))"} >} STBEL)

Re: YTD last month

bbi_mba_76 — Thu, 05 Jun 2014 10:04:23 GMT

Hi,

if I understand well you don't need the rolling months, you need as min date Jan, 1st and then the year

something like makedate(year(Max(MonthYear)))

Re: YTD last month

Thu, 05 Jun 2014 10:17:01 GMT

But I don't want the current month to be calculated in the YTD

Re: YTD last month

bbi_mba_76 — Thu, 05 Jun 2014 10:24:59 GMT

the second part of your expression is ok, no?

I think you have only to modify the start date (first part) to set Jan the 1st

Re: YTD last month

Thu, 05 Jun 2014 10:35:14 GMT

Hi Johan,

Have you tried the YearToDate() function? It returns a boolean value if the date matches with the date interval specified into the parameters. You can do a sum until date value is false. Use help to check the syntax details and parameters.

Regards.

Re: YTD last month

Thu, 05 Jun 2014 10:50:52 GMT

The second part is working fine.

Since I'm new to QV I'm having a hard time to get it to work. I'm not sure exactly how the make date function works and google isn't very helpful either 😃

Re: YTD last month

Thu, 05 Jun 2014 10:53:58 GMT

Hi Jesus,

The thing is that I'm trying to do as much as possible within the expressions

I'll not set any specific parameters like month or year. If I choose to do so my expressions for last year ect stops working 😃

Re: YTD last month

bbi_mba_76 — Thu, 05 Jun 2014 10:57:03 GMT

this is not verified, I don't have a qvw...

Sum({<MonthYear ={">=$(=date(makeDate(year(Max(MonthYear))), 'MMM-YY')) <=$(=Date(addmonths(Max(MonthYear), -1), 'MMM-YY'))"} >} STBEL)

Re: YTD last month

Thu, 05 Jun 2014 11:01:28 GMT

Thanks Michele, it works perfect....