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: 
Not applicable

Selecting from begining of the year to last month

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.

8 Replies
Marcio_Campestrini
Specialist
Specialist

Hi!

Have you tried to use this for last period: 'Month & Year' = Date(Today, 'MM-YYYY')?

Márcio Rodrigo Campestrini
Not applicable
Author

Thanks for your response but, What do you mean by that? Could you explain.

Marcio_Campestrini
Specialist
Specialist

I suggest you to use the instruction Date(Today, 'MM-YYYY') in your set analysis, just to obtain the last Month/Year.

Márcio Rodrigo Campestrini
Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

Hi

Can you share your QVW file?

Thanks

Sabal

VishalWaghole
Specialist II
Specialist II

Hi,

YearStart() will give the Starting Month of this year

and

Addmonths(Month(Max(Date)),-1) this will gives last Month.

-- Regards,

Vishal Waghole