Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i am working on YTD calculation and i have week and year as a parameter.
Sum({1 <WEEK={">}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"}, Year = {$(=MAX(Year))}>}M_COUNT_WEEK)
when i am using the above query its always fetching me the max of year. ideally it should give me the data for the year that i am selecting. i have 2 years 2016 and 2015 when i am selecting 2015 it still shows me 2016 data. please help.
This expression should work:
Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year))}>} M_COUNT_WEEK)
If that is not working, you might want to post a small sample qvf file for more detailed understanding of your model and more accurate help.
And yes, this should work no matter how many years you have. If you want to compare the latest (or selected) year with the previous year:
Current/selected year
Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year))}>} M_COUNT_WEEK)
Prior year (to same week number as current/selected year):
Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year)-1)}>} M_COUNT_WEEK)
Hi
First of all , I do not understand the selection you made . Seems to be some error
1)Please check highlighted part .
Sum({1 <WEEK={">}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"},
Year = {$(=MAX(Year))}>} M_COUNT_WEEK)
2)
you have declared Year = {$(=MAX(Year)) , AND '1' in the expression due to this i think year always slects MAX year i.e 2016 !!?
Sum({1 <WEEK={">}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"},
Year = {$(=MAX(Year))}>} M_COUNT_WEEK)
Hi
Sum({<WEEK={">=$(=Max({<Year={$(=MAX(Year)-1)}>}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"}, Year = {$(=MAX(Year))}>}M_COUNT_WEEK)
try this.
Regards,
Hi Alluraiah,
the query is Sum({1<WEEK={"<=$(=Max({<Year={$(=Max(Year))}>}WEEK))"},Year = {$(=Max(Year))}>}M_COUNT_WEEK)
Can you let me know what might me the correct syntax for year?
Thanks Marco.
But this is giving me exact value for that week. but i need ytd value.
Hi Marco
If i have 3 years then will this condition work?
Sum({<WEEK={">=$(=Max({<Year={$(=MAX(Year)-1)}>}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"}, Year = {$(=MAX(Year))}>}M_COUNT_WEEK)
This expression should work:
Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year))}>} M_COUNT_WEEK)
If that is not working, you might want to post a small sample qvf file for more detailed understanding of your model and more accurate help.
And yes, this should work no matter how many years you have. If you want to compare the latest (or selected) year with the previous year:
Current/selected year
Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year))}>} M_COUNT_WEEK)
Prior year (to same week number as current/selected year):
Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year)-1)}>} M_COUNT_WEEK)
Thank you very much Jonathan. Its working.
Thanks to others as well for helping me out in this problem.
If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).
If not, please make clear what part of this topic you still need help with .
Hi Jonathan,
I want to compare week on week value
that is current week - previous week / previous week
how can we achieve that?
ideally it should also cover if current week is 1 so the previous week will be max of week of the previous year.