Configuring an analysis to automatically use the previous month in the date column.

Question

I have an analysis that I want to send out automatically once monthly. Is there a way to configure the analysis to use the previous month's data? I currently have it set up to pull anything greater than or equal to the first of the year, and users will filter out the previous months.

Thomas Dodds · Answer

OR build a proper Calendar dimension and dispense with the calculations each time you need a variation on it.  Calc ONCE use MANY ...

Jerry Casey · Answer

Oops.  That one is for two month before the present.  Use this one:

"Table"."Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH,-1,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)-1),CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)),CURRENT_DATE)

Jerry Casey · Answer

This filter returns dates from the previous month:

"Table"."Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH,-2,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)-1),CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_MONTH,-1,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)),CURRENT_DATE))