Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have a field 'Month & Year' where the values include: Dec-2013,Jan-2014, Feb-2014, Mar-2014 ...and so on till the end of this year.
So, I needed help figuring out the set analysis that just included the beginning of this year which is Jan-2014 till the last month which is Jun-2014 and this has to be dynamic so if the month is AUG-2014, the set analysis will automatically calculate until last month i.e. JUL-2014. Any help will be really appreciated.
Thanks.
Hi!
Have you tried to use this for last period: 'Month & Year' = Date(Today, 'MM-YYYY')?
Thanks for your response but, What do you mean by that? Could you explain.
I suggest you to use the instruction Date(Today, 'MM-YYYY') in your set analysis, just to obtain the last Month/Year.
Thanks for your answer, but I think you mean to use =Date(addmonths(today(),-1),'MMM-YYYY') for last month and =Date(YearStart(today()),'MMM-YYYY') for the year beginning, I have got that part but somehow I need to get those two into set analysis, I need help with that.
Thanks.
Hi
If you need to use the set analysis to calculate a value from beginning of the year to last month of the year, then refer to below analysis for Sales:
Create 2 variables:
vBegMonth = Date(YearStart(today()),'MMM-YYYY')
vLastMonth = Date(addmonths(today(),-1),'MMM-YYYY')
Expression for Sales = sum({<[Month & Year] = {">=$(vBegMonth)<=$(vLastMonth)"}>}Sales)
Thanks
Sabal
Thanks Sabal,
that is exactly what I'm doing but it shows the values as 0. When I try
=sum({<[Month & Year]={'$(vLastMonth)'}>}Sales) it picks up the values for last month but when I do what you suggested it shows 0 as a resuts. Any advice?
Thanks.
Hi
Can you share your QVW file?
Thanks
Sabal
Hi,
YearStart() will give the Starting Month of this year
and
Addmonths(Month(Max(Date)),-1) this will gives last Month.
-- Regards,
Vishal Waghole