Run analysis for the previous month data only

user1106547

i want to run an analysis that displays data for the previous month.

so if i set up an agent to run say 1st day of every month, the data would display for the preceding month only.

i have created this field TIMESTAMPDIFF(SQL_TSI_MONTH,"Date (procedure)"."Date",CURRENT_DATE) which returns a value i thought i could filter on but the value returned isn't accurate.

For example if i run the report  today to include data up to 26th july , early July values are 1 while later july values are 0. i would expect all july values to be 0, all june 1 etc.

Anyway above is probably not  the right approach. Is there a simpler and accurate way to achieve this?

Any help appreciated

thanks

Andrew Fomin.

That may be confusing, but that's how TimeStampDiff works. If you take a look at the SQL generated for your report, you'll see something like (for Oracle DB):

ROUND( MONTHS_BETWEEN( TO_DATE('2016-07-26' , 'YYYY-MM-DD'), T1714.DAY ) ).

This formula returns 0 for values starting with 11th July and ending with 10th August.

I'd say the best choice is to use a repository variable for the previous month. Or you can play around with Month(...) and Year(...) functions. But I think a repository variable approach is much better.

user1106547

thanks for suggestion re variables but in the end just used below as filter:

First day of previous month

TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Last day of previous month

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

seems to work ...