Displaying Previous Month Data

[k2]

I have a report which needs to display the values for month selected by the user via value prompt and the previous month. For example, if the user selects 'December 2009' via value prompt. List Report should display the value for 'December 2009' along with 'November 2009'. In order to achieve this ,I have tried with the following condition for the data item 'Selected Month -1'but it is displaying the same value as 'Selected Month', no change.

[Selected Month] --> [Amount]

[Selected Month -1] -->

if ([Dates].[Days].[Date]=?Period?)

then (total([Amount] for _add_months([Dates].[Days].[Date],-1)))

else (0)


[Dates].[Days].[Date]=?Period? --> is the filter condition for prompt.

Please let me know, where I went wrong. It would be a great help. Thanks 

[blom0344]

[Selected Month -1] -->

Code Select Expand


CASE WHEN
[Dates].[Days].[Date]
between
_first_of_month(_add_months(?Period?,-1))
and
_last_of_month(_add_months(?Period?,-1))
THEN
[amount]
ELSE
(0)
END


assuming ?Period?  relates to a date
set aggregate as total

[k2]

Thanks for the response. Data item was aggregated as Total but no luck!!!

[Mpotla]

Hi,
Use the below case stmt to aggregate the values for  selected month and previous month of it.
its small modification to the above reply.



CASE WHEN
[Dates].[Days].[Date]
between
_first_of_month(_add_months(?Period?,-1))
and
_last_of_month(?Period?)
THEN
[amount]
ELSE
(0)
END

Regards,
Potla

[mvjcognos]

u can try this also

if to_date(date key,'YYYYMMDD') between _first_of_month(_add_months(?date?,-1) and ?date? then (sales quantity) else (0)