URGENT Help needed for a Date filter

[KEKeogh]

I have the following filter to grab the 1st of the year through the last day of the previous month.

[CDATE] between _make_timestamp(extract(year,current_date),1,1) and _add_days(_first_of_month(current_date),-1)

This works great every month BUT January.  Where it pulls right now 1/1/2011 through 12/31/2010.

Anyone know of a filter that will help??

Thanks
Kathie

[Lynn]

Assuming the desired result for this month would be 1/1/2010 through 12/31/2010, then you could replace the make timestamp portion with:

Code Select Expand

_make_timestamp(extract(year,_add_months(current_date,-1)),1,1)

This grabs the year for the prior month and makes a timestamp of Jan 1 for that year. Once February rolls around it will resolve to Jan 1 2011.

[KEKeogh]

Lynn, you are a life saver!!!!!  ;D

That worked perfectly.  I knew it had to be something simple but the harder I looked the more it eluded me.

Now the clients won't be getting incorrect reports tomorrow that I have to go back and re-run.

Thanks

[Lynn]

Great Kathie! Plan on a long lunch tomorrow instead :D

[stancho]

Hi, I tried this filter:

[DS][TimeDimension].[Date] between _make_timestamp(extract(year,_add_months(current_date,-1)),1,1) and _add_days(_first_of_month(current_date),-1)

But when I try to validate I have this error:
QE-DEF-0459 CCLException
QE-DEF-0260 Parsing error before or near position .. of [DS][TimeDimension].[Date] between _make_timestamp(extract(year"
QE-DEF-0261 QFWP - Parsing text: [DS][TimeDimension].[Date] between _make_timestamp(extract(year,_add_months(current_date,-1)),1,1) and _add_days(_first_of_month(current_date),-1)

My datasource is MS SQL.
[DS][TimeDimension].[Date] is Date

Any ideas?

Stancho

[Lynn]

The best way to debug this type of thing is to create data items in a query for the two parts of your between clause. Build each expression up one layer at a time until they evaluate to what you want.

So for the first one, start with the innermost part: _add_months(current_date,-1)

Then wrap it with the next part for the extract....and so on. Once you've gotten the syntax sorted out you can get rid of the data items. I think the issue might be with the extract, but not really sure.

This is not the specific answer to your question, but along the lines of the proverb "Give a man a fish and he'll eat for a day. Teach a man to fish and he will eat for a lifetime."

My husband would add to this "...and if you want the fish to be edible, don't let Lynn cook it."  ;D

[stancho]

Thanks Lynn,

I like your answer because you're very right about the fish...

I will debug it later!

[MFGF]

How do you know?  Have you been round to Lynn's for dinner?? :)