Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
May be like this? PFA.
Please share your sample app.
Finally managed to figure out how to upload my example...
May be like this? PFA.
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...)?
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.
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.
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), '#,##%')