Exclude current month with set analysis

[#1]

Hi all.

I need to exclude the current month in a set expression like this:

Count({$<Product={'Blue'}>} TransID)

/

Count({$<ListEnabled={'1'}>} DISTINCT CustID)

In the chart I use "yearmonth"(apr 2012) as a dimension.

Thanks!

[MayilVahanan]

HI

Try like this

Count({$<Product={'Blue'},yearMonth-={$(MonthName(Today()))}>} TransID)

/

Count({$<ListEnabled={'1'},yearMonth-={$(MonthName(Today()))}>} DISTINCT CustID)

Hope it helps

[#3]

Thank you for quik respons. But I get something wrong with that expression and "-=".

Any suggestions what it can be?

[MayilVahanan]

Hi

     Is not error, its bug in qv expression.. Expression ok..

[jagan]

Hi,

It is bug in QV when you use -= in Set Analysis expression, the -= is underlined with red color.  It shows as error but it works.

Regards,

Jagan.

[#6]

It still not working. Could it be some problems regarding the date format? Is it needed to type the format like date(mm-yyyy)?

[jagan]

Hi,

The date format should be the same.  You can format the date by using Date().

Regards,

Jagan.

[#8]

Like this:

Count({$<yearMonth-={"$(=Date(MonthName(Today())),'mmm YYYY')"}, Product={'Blue'}>} TransactionID)

/

Count({$<yearMonth-={"$(=Date(MonthName(Today())), 'mmm YYYY')"}, ListEnabled={'1'}>} DISTINCT CustomerID)

[whiteline]

Hi.

It seems the problem is that Today() returns a numerical date with 'days', while in your yearMonth field it's just the first day of month (you just hide the days by formatting).

Try to use MonthStart() function instead of MonthName() in your expression, this should solve it.

Also to test your expressions and calculations you can create two textbox with expressions:

=yearMonth=Date(MonthStart(Today())),'mmm YYYY')

=Date(MonthStart(Today())),'mmm YYYY')

Then select current year value in yearMonth (in a listbox).

The first textbox should show '-1' (this means that yearMonth equals your expression) and the second will show the resulting date format.