Previous Month

[former_member729889]

Hi,

I have two seperate dimensions for year and month. I'm calculating a measure through a prompt for the current month.

I've used the where clause part for the current month like:

Count(Measure)

*where clause*

Table.Year =  @Select(Filters\Year) AND

Table.Month =  @Select(Filters\Month)

My question is, how can I derive the previous month in a second measure?

Thanks!

[Former Member]

Steve,

I wouldn't recommend using where clauses - they will cancel each other out as they both get added to the SQL so your pseudo will end up as something like:

SELECT this, that, theother, count measures

FROM table1, table2

WHERE

transactionmonth = current month

AND

transactionmonth = previous month

The best practice is to use CASE statements to create your two objects as:

count(CASE when month = previous month THEN countitem END)

count(CASE when month = current month THEN countitem END)

Obviously using whatever logic you need to identify the months.

Does that make sense?

[Former Member]

Hi,

Put this is your where clause

Previous Month :

to_char(ADD_MONTHS (TO_DATE (concat( to_char(( current_date ),'YYYYMM') ,'01'), 'YYYYMMDD'), -1),'MMYYYY')

This for current date it will give you 032015 as current month is april 2015 , Replace current date with your date object if you want it for some other date.

Thanks,

Haseeb

[Former Member]

Hi Steve,

You can simple use add_months(variable,-1) for getting previous year.

Thanks,

Swapnil

[former_member729889]

Hi,

add_months requires a full date dimension; which I don't have.

[Former Member]

Hi Steve,

Can you please explain me your requirement clearly ?