Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How to calculate YTD amount

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

 

 

3 Solutions

Accepted Solutions
Highlighted
Contributor
Contributor

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)

View solution in original post

Highlighted
Creator III
Creator III

Partner
Partner

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)  
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

6 Replies
Highlighted
Contributor
Contributor

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)

View solution in original post

Highlighted
Creator III
Creator III

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

Highlighted
Creator III
Creator III

Partner
Partner

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)  
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Highlighted

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor III
Contributor III

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