topic Re: How to calculate YTD amount in App Development

How to calculate YTD amount

letileti — Thu, 15 Nov 2018 20:37:11 GMT

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

Re: How to calculate YTD amount

camflawless — Thu, 15 Nov 2018 20:43:37 GMT

It may be easier to create a flag in your data set in the load script editor and use that within an expression, making use of set analysis.

i.e.

=sum({< ytd_flag = {1}>} measure_value)

Re: How to calculate YTD amount

jayanttibhe — Thu, 15 Nov 2018 20:51:45 GMT

Aggr(RangeSum(Above(Sum([Actuals]),0,RowNo())), (Year, Month, (NUMERIC)))

Re: How to calculate YTD amount

jayanttibhe — Thu, 15 Nov 2018 20:54:26 GMT

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

Re: How to calculate YTD amount

Vegar — Thu, 15 Nov 2018 21:27:41 GMT

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)

Re: How to calculate YTD amount

Anil_Babu_Samineni — Fri, 16 Nov 2018 05:27:44 GMT

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)

Sum({<DateField = {">=$(=Date(YearStart(Max(DateField))),'Your Date Field Format')<=$(=Date(AddMonths(MonthEnd(Max(DateField)),-1)),'Your Date Field Format')"}>} Sales)

Re: How to calculate YTD amount

letileti — Fri, 16 Nov 2018 19:03:05 GMT

Thanks a lot for your support！I used the flag in script, works perfect