Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
letileti
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
camflawless
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

jayanttibhe
Creator III
Creator III

Vegar
MVP
MVP

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)  

View solution in original post

6 Replies
camflawless
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)
jayanttibhe
Creator III
Creator III

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

jayanttibhe
Creator III
Creator III

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

Vegar
MVP
MVP

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)  
Anil_Babu_Samineni

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
letileti
Contributor III
Contributor III
Author

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