calculating next month for December month

[Former Member]

Hi,

I am able to get the value for a measures in which one measure calculates value for month selected through prompt and another measure calculates values based on next month.

So I have restriced measure 1 like this in where condition

MONTH_V.MONTH_IN_YEAR_NUM=@Prompt('2. Month :','A','Prompt\Month',Mono,Constrained)

Second measure I have restricted like this

MONTH_V.MONTH_IN_YEAR_NUM=(to_number(Substr(@Prompt('2. Month :','A','Prompt\Month',Mono,Constrained),1,2))+1)

This works perfectly alright.

I have prompt for year also. So when I select December - 08 then the calculation goes wrong, since MONTH_IN_YEAR_NUM=13 and there is no corresponding record for 13.

So my question here is can I use CASE statement for this.

MONTH_V.MONTH_IN_YEAR_NUM=

(CASE WHEN (to_number(Substr(@Prompt('2. Month :','A','Prompt\Month',Mono,Constrained),1,2))<>12 THEN

(to_number(Substr(@Prompt('2. Month :','A','Prompt\Month',Mono,Constrained),1,2))+1)

ELSE 1

)

AND

MONTH_V.MONTH_YEAR=

CASE WHEN (to_number(Substr(@Prompt('2. Month :','A','Prompt\Month',Mono,Constrained),1,2))<>12 THEN

(@Prompt('3. Year :','N','Prompt\Year', mono ,constrained)

ELSE

(to_number(@Prompt('3. Year :','N','Prompt\Year', mono ,constrained)+1)

)

[Former Member · Accepted solution]

Hi Raghavendra,

Instead of To_number use To_date function as

MONTH_V.MONTH_IN_YEAR_NUM=@Prompt('2.Month :','A','Prompt\Month',Mono,Constrained)

Second measure I have restricted like this

MONTH_V.MONTH_IN_YEAR_NUM=(to_date(Substr(@Prompt('2. Month:','A','Prompt\Month',Mono,Constrained),1,2))+1)

[Former Member]

Hi,

Create 2 condition objects with prompt in universe. one for current month and another for next month from current month.

In report take data from 2 data providers. One for current month and one for next month and apply the corresponding conditions and also take the corresponding measures.

Hope this will help you.

Regards,

Rakesh K