cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Query Builder Criteria Logic for getting last month

Highlighted
Guest

Query Builder Criteria Logic for getting last month

Hi,

It's that time of year again when 1-1=0.

I use the Get_Month - 1 calculation to capture the work items created last month.

Problem is in January the month = 1

For some reason 1 minus 1 does not equal 12...

Has anyone a better way to get a criteria for 'last month'?

This goes into a Cognos report, so I'm not able to use the 'last month' attributes when running the report with Report Builder

5 REPLIES 5
Highlighted

Re: Query Builder Criteria Logic for getting last month

Can you insert an if/else check in there to look at the result of Get_Month - 1 and if it equals 0 then set it to 12?  You'd also have to then subtract 1 from the year too ...

Highlighted

Re: Query Builder Criteria Logic for getting last month

I have not found any if/else/case logic on the query builder. Can you send me a screenshot?

Highlighted

Re: Query Builder Criteria Logic for getting last month

That's a good point - I think you could only use if/else/case logic outside of query builder, but inside of it maybe you can use the "Time Difference(days)" comparing the Work Item.Last Modified attribute to sysdate, and you can at least get the results from the past 30 days?  Granted if you ran it in the middle of the month you'd get from mid-month to the previous mid-month ...

Another alternative is to just create a report that gives you the results for a particular month (like Dec 2015), instead of "last month", and then each month duplicate the report and just update the new one for the next month (Jan 2016).

Highlighted

Re: Query Builder Criteria Logic for getting last month

Hello Vaugn,

You can get last 'N' days report i.e 30 or 90.... just by using  time difference function with system date and Corresponding date field as arguments. Check below criteria.

Highlighted

Re: Query Builder Criteria Logic for getting last month

I am in PDMLink 10.1 and trying to report the last 30 days a CN has been completed (Resolution Date).  What am I doing wrong.  It works fine to report all CN's without it.  Once I add this it reports nothing.

 

And I am assuming the <Last_30> is a Constant?

 

I tried using the create stamp as shown but I also tried using the Resolution Date just for the heck of it.

 

Last n days-reporting.jpg

Announcements