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

vmcdaniel
2-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

6 REPLIES 6
bsindelar
5-Regular Member
(To:vmcdaniel)

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 ...

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

bsindelar
5-Regular Member
(To:vmcdaniel)

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

kchaithanya
6-Contributor
(To:vmcdaniel)

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.

STEVEG
20-Turquoise
(To:kchaithanya)

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

rfischer-2
5-Regular Member
(To:vmcdaniel)

In Windchill 11.1 I was able to set a criteria with days while using the Database Function Time difference;

 

rfischer-2_0-1619782972648.png

 

 

Announcements
Learn more about how we are using our browser poll here