topic Re: Exclude current month with set analysis in QlikView

Exclude current month with set analysis

Tue, 18 Sep 2012 14:27:36 GMT

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!

Re: Exclude current month with set analysis

MayilVahanan — Tue, 18 Sep 2012 14:31:48 GMT

Try like this

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

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

Hope it helps

Re: Exclude current month with set analysis

Tue, 18 Sep 2012 14:37:56 GMT

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

Any suggestions what it can be?

Re: Exclude current month with set analysis

MayilVahanan — Tue, 18 Sep 2012 14:39:53 GMT

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

Re: Exclude current month with set analysis

jagan — Tue, 18 Sep 2012 14:57:00 GMT

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.

Re: Exclude current month with set analysis

Mon, 24 Sep 2012 08:25:32 GMT

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

Re: Exclude current month with set analysis

jagan — Mon, 24 Sep 2012 12:04:27 GMT

Hi,

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

Regards,

Jagan.

Re: Exclude current month with set analysis

Mon, 24 Sep 2012 13:08:30 GMT

Like this:

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

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

Re: Exclude current month with set analysis

whiteline — Thu, 27 Sep 2012 06:58:48 GMT

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.