Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
An issue has been identified on Qlik Cloud hub, please visit our Status Update Page for details: GET THE LATEST
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Simple Set Analysis Expression

I am using the formula below...

=Date(Max([Event YearMonth]-1),'MMM-YYYY')

And it is returning Sep-2014.

However, if I insert any other number (2, 3, 4, etc.) it will still return Sep-2014.

Does anyone know why? I'm trying to show as below.

=Date(Max([Event YearMonth]),'MMM-YYYY') - current month --> Ok

=Date(Max([Event YearMonth]-1),'MMM-YYYY') - previous month --> Ok

=Date(Max([Event YearMonth]-2),'MMM-YYYY') - 2 months ago --> Returns Sep-2014 (same as above)

1 Solution

Accepted Solutions
bill_markham
Champion III
Champion III

[Event YearMonth] is I reckon stored as a numeric date, but as a dual displays as MMM-YYYY.


So subtracting 1, subtracts 1 day and not 1 month.  Which would give you the results you desribe.


Try the AddMonths() function.  I have pasted in the Help Text for it and you can pass a negative value into it as you need.




AddMonths(startdate, n , [ , mode] )

Returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.

By specifying a mode (0 if omitted) the date is set to either the unmodified day of the specified month (mode=0) or the calculated day as derived from the end of the month (mode=1).

Examples:

addmonths ('2003-01-29',3) returns '2003-04-29'

addmonths ('2003-01-29',3,0) returns '2003-04-29'

addmonths ('2003-01-29',3,1) returns '2003-04-28'

addmonths ('2003-01-29',1,0) returns '2003-02-28'

addmonths ('2003-01-29',1,1) returns '2003-02-26'

addmonths ('2003-02-28',1,0) returns '2003-03-28'

addmonths ('2003-02-28',1,1) returns '2003-03-31'

View solution in original post

9 Replies
jaimeaguilar
Partner - Specialist II
Partner - Specialist II

Hi,

Could you please upload a sample of your data in order to help you better?

regards

kimrormark
Creator
Creator

Does this work?


=Date(Max([Event YearMonth])-2,'MMM-YYYY')

bill_markham
Champion III
Champion III

[Event YearMonth] is I reckon stored as a numeric date, but as a dual displays as MMM-YYYY.


So subtracting 1, subtracts 1 day and not 1 month.  Which would give you the results you desribe.


Try the AddMonths() function.  I have pasted in the Help Text for it and you can pass a negative value into it as you need.




AddMonths(startdate, n , [ , mode] )

Returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.

By specifying a mode (0 if omitted) the date is set to either the unmodified day of the specified month (mode=0) or the calculated day as derived from the end of the month (mode=1).

Examples:

addmonths ('2003-01-29',3) returns '2003-04-29'

addmonths ('2003-01-29',3,0) returns '2003-04-29'

addmonths ('2003-01-29',3,1) returns '2003-04-28'

addmonths ('2003-01-29',1,0) returns '2003-02-28'

addmonths ('2003-01-29',1,1) returns '2003-02-26'

addmonths ('2003-02-28',1,0) returns '2003-03-28'

addmonths ('2003-02-28',1,1) returns '2003-03-31'

maxgro
MVP
MVP

what's the format of   [Event YearMonth]?

could you post some values of this field?

Not applicable
Author

That does not work.

jyothish8807
Master II
Master II

Hi DD,'

Try like this:

=month(max(Date))

=month(addmonths(max(Date),-1))  for previous month

Regards

KC

Best Regards,
KC
Not applicable
Author

This is correct.

It is only subtracting 1 day...

I removed the Date expression and I'm getting below.

=Max(EVENT_YearMonth)-1 - 41882

=Max(EVENT_YearMonth)-2 - 41881

I have to run to a meeting. Let me play with this and I will get back to you. Thank you so much.

Not applicable
Author

Thank you everyone for their prompt replies!!!

I was able to use below.

=date(addmonths(max(EVENT_YearMonth),-1),'MMM-YYYY')

=date(addmonths(max(EVENT_YearMonth),-2),'MMM-YYYY')

=date(addmonths(max(EVENT_YearMonth),-3),'MMM-YYYY')

Not applicable
Author

Thank you for your advice Bill!


I used the formulas below... this will catch the rollover in terms of year, correct?

For example, if they current year is Jan. 2015, the below formulas will capture Dec. 2014, Nov. 2014?

=date(addmonths(max(EVENT_YearMonth),-1),'MMM-YYYY')

=date(addmonths(max(EVENT_YearMonth),-2),'MMM-YYYY')