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: 
martin_cevela
Contributor III
Contributor III

How to SUM for YTM ?

Hi there,

Is there a way to SUM for YTM? I need to compare the sum of revenue for current year with the sum of forecast from begin of the year until the month selected. Examples found are only returning the SUM for the selected month and not the SUM for YTM. I`m new to QV and have found X ways that claim YTM solution, but unfortunately none of them works for me…

num(

SUM({ <Year={'$(CurrentYearID)'}> } AmountEURFixed)

/sum({<Year={'$(CurrentYearID)'},MonthID = {'<=$(CurrentMonthID)'}>} ForecastEURFixed)

,'#,##%')

This expression returns the SUM for selected month (AmountEURFixed & ForecastEURFixed) or if no value selected then the Current Year SUM for AmountEURFixed & YTM SUM for ForecastEURFixed. However, what would be the amendment need to get the dynamic SUM for YTM?

Thanks for any help here!


1 Solution

Accepted Solutions
tresesco
MVP
MVP


May be like this? PFA.

View solution in original post

7 Replies
tresesco
MVP
MVP

Please share your sample app.

martin_cevela
Contributor III
Contributor III
Author

Finally managed to figure out how to upload my example...

tresesco
MVP
MVP


May be like this? PFA.

martin_cevela
Contributor III
Contributor III
Author

Exactly like this! Thanks for your time Treseco.
I have found this expression and natively removed the date part...

So, I got the fish, but want to learn how to catch too (if you can spare one more reply), and hence
would like to understand why the "Date" is needed here although data
is aggregated to months (coming out of SQL world...)?
 
 








tresesco
MVP
MVP

If you don't include Date, then also you can get the desired result, but then you have to bypass the Date field selection and write the set expression accordingly. With the above solution you have the fexlibility to get the dynamic YTD as well.

martin_cevela
Contributor III
Contributor III
Author

Ehm, have one more Q related - the user is selecting instead the MonthID - the MonthName which is a non-numeric field

I`m struggling to adapt the logic to the MonthName.

martin_cevela
Contributor III
Contributor III
Author


ok - sorry for being so dummy

This is the amendment I was looking for....

num(Sum({<Year={'$(CurrentYearID)'},MonthID={"<=$(=Max(MonthID))"}, MonthName=>}AmountEURFixed)
/
Sum({<Year={'$(CurrentYearID)'},MonthID={"<=$(=Max(MonthID))"}, MonthName=>}ForecastEURFixed), '#,##%')