Qlik Community

Qlik Sense App Development

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

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
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

Highlighted
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

Creator III
Creator III

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

Highlighted
Creator III
Creator III

Highlighted
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