Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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])
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
Hi Sunny,
Thanks a lot,Its working....
Really Thankful towards your great efforts....
Regards,
Priyal !
Hi Sunny,
Will you please explain me this so that i can understand the logic.
Thanks,
Priyal
Refer this:
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?
Thanks a lot Sunny....
Really its pretty helpful info.
Regards,
Priyal.