Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I don’t manage to calculate YTD amount and it is driving me nuts.
someone who Can help me providing correct syntax?
Expected result 2018 YTD Oct = 320. I have a variable Vlastmonth which returns October.
i always need the YTD for current year (January until last month).
Year, Month, Actuals
2018,jan,100
2018,feb,20
...
2018,Oct,200 (last month)
2018,Nov,200
You can also check this if you want to use the script option
https://community.qlik.com/t5/QlikView-App-Development/YTD-Year-to-date/td-p/582412
If your YTD.month is static you could flag your YTD dates i the script.
sum({<YtdFlag = {'1'}>}Actuals)
If you don't have access to the script then you could do the following expression. Assuming that vYtdMonth is a digit 1-12.
sum({< Month= {"<$(vYtdMonth)"}>} Actuals)
If you also want to limit the year you could include a Year modifier
sum({< Year= {"$(=max(Year))"}, Month= {"<$(vYtdMonth)"}>} Actuals)
Aggr(RangeSum(Above(Sum([Actuals]),0,RowNo())), (Year, Month, (NUMERIC)))
You can also check this if you want to use the script option
https://community.qlik.com/t5/QlikView-App-Development/YTD-Year-to-date/td-p/582412
If your YTD.month is static you could flag your YTD dates i the script.
sum({<YtdFlag = {'1'}>}Actuals)
If you don't have access to the script then you could do the following expression. Assuming that vYtdMonth is a digit 1-12.
sum({< Month= {"<$(vYtdMonth)"}>} Actuals)
If you also want to limit the year you could include a Year modifier
sum({< Year= {"$(=max(Year))"}, Month= {"<$(vYtdMonth)"}>} Actuals)
I always consider YTD is Year to date only. If My understand is correct I suggest to use this?
Sum({<DateField = {">=$(=YearStart(Max(DateField)))<=$(=AddMonths(MonthEnd(Max(DateField)),-1))"}>} Sales)
Or
Sum({<DateField = {">=$(=Date(YearStart(Max(DateField))),'Your Date Field Format')<=$(=Date(AddMonths(MonthEnd(Max(DateField)),-1)),'Your Date Field Format')"}>} Sales)
Thanks a lot for your support!I used the flag in script, works perfect