topic Re: Simple Set Analysis Expression in QlikView

Simple Set Analysis Expression

Tue, 07 Oct 2014 16:50:55 GMT

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)

Re: Simple Set Analysis Expression

jaimeaguilar — Tue, 07 Oct 2014 16:54:52 GMT

Hi,

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

regards

Re: Simple Set Analysis Expression

Anonymous — Tue, 07 Oct 2014 16:59:53 GMT

Does this work?

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

Re: Simple Set Analysis Expression

Anonymous — Tue, 07 Oct 2014 17:01:09 GMT

[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'

Re: Simple Set Analysis Expression

maxgro — Tue, 07 Oct 2014 17:02:31 GMT

what's the format of [Event YearMonth]?

could you post some values of this field?

Re: Simple Set Analysis Expression

Tue, 07 Oct 2014 17:04:53 GMT

That does not work.

Re: Simple Set Analysis Expression

jyothish8807 — Tue, 07 Oct 2014 17:05:22 GMT

Hi DD,'

Try like this:

=month(max(Date))

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

Regards

Re: Simple Set Analysis Expression

Tue, 07 Oct 2014 17:06:36 GMT

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.

Re: Simple Set Analysis Expression

Tue, 07 Oct 2014 18:00:59 GMT

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')

Re: Simple Set Analysis Expression

Tue, 07 Oct 2014 18:15:36 GMT

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')