How to calculate the YTD for previous month.

[priyalvp24]

Hi all,

I want to calculate the ytd up to previous month.(Current month-1)

e.g. if my current month is August THEN YTD SHOULD BE UP TO 31st July.

My YTD Set Analysis statement is

sum({<DocYear={'$(=max(DocYear))'}>}    [Budget in Rs])

MTD Set Analysis statement is

sum({<DocMonth={"$(=max({<DocYear={'$(=max(DocYear))'}>}DocMonth))"}>}    [Budget in Rs])

My Max date in Budget sheet is 31/03/2019.

Please Help...

Thanks,

Priyal

[sunny_talwar · Accepted solution]

What is the format of DocMonth? Is it a number formatted or does it look like Jan, Feb, Mar... etc? If it is later, create another field like this

Num(Month(DocDate)) as DocMonthNum

and then this expression

Sum({<DocYear = {'$(=Max(DocYear))'}, DocMonthNum = {"<$(=Num(Month(Today())))"}>} [Budget in Rs])

[Anonymous]

Hi

You can use below expression to achieve result for previous month YTD.

=sum({<cdate={">=$(=Floor(YearStart(Today())))<=$(=Floor(MonthEnd(Today(),-1)))"}>}[Total Profit])

Thanks

[priyalvp24]

Hi Sunny,

Thanks a lot,Its working....

Really Thankful towards your great efforts....

Regards,

Priyal !

[priyalvp24]

Hi Sunny,

Will you please explain me this so that i can understand the logic.

Thanks,

Priyal

[balabhaskarqlik]

Refer this:

YTQ, QTD, MTD and WTD

[balabhaskarqlik]

https://www.qlikcommunity.org/message/551854

[sunny_talwar]

Couple of things

1) Set analysis is format sensitive... so when you try to do Jan <= Mar.... false because QlikView isn't able to do the comparison. So, in order to avoid this... I proposed to use Month Number (1 <= 3... true)

2) All the expression is doing is to look at the most recent year and Month Number less than the month number for the current date. So, today is 8/31/2018... so basically pick all months for current year where the month is less than 8.

Does that make sense?

[priyalvp24]

Thanks a lot Sunny....

Really its pretty helpful info.

Regards,

Priyal.