To get Current,Last, Next months data for Efashion report

[Former Member]

To Create a report with the user input prompt on Year and Month:

If user select year as 2005 and month as 2 (Feb), revenue should be displayed for previous Month (Jan) and next month (March) of selected year.

Sample output should looks as below.

Year : 2005
State	Prev Month - Jan	Current Month - Feb	Next Month - Mar
AAA	$100	$100	$600
BBB	$200	$400	$200
CCC	$300	$300	$300

I have created dimensions in universe premonth=(@Select(Time period\Month) -1) & Nextmonth=@Select(Time period\Month) +1) & taken 3 dataproviders from efashion universe .these are not working for year ends

[Former Member · Accepted solution]

Hi

solution found, taken 3 data providers & added below filters for 2 &3 data providers

DP2

------

@Select(Time period\Year)=iif(@Prompt('Enter Month','N','Time period\Month',,)=12,@Prompt('Enter Year','A','Time period\Year',,)+1,@Prompt('Enter Year','A','Time period\Year',,))

@Select(Time period\Month)=iif(@Prompt('Enter Month','N','Time period\Month',,)=12,1,@Prompt('Enter Month','N','Time period\Month',,)+1)

DP3

-----

@Select(Time period\Year)=iif(@Prompt('Enter Month','N','Time period\Month',,)=1,@Prompt('Enter Year','A','Time period\Year',,)-1,@Prompt('Enter Year','A','Time period\Year',,))

@Select(Time period\Month)=iif(@Prompt('Enter Month','N','Time period\Month',,)=1,12,@Prompt('Enter Month','N','Time period\Month',,)-1)

Thanks

[Former Member]

HI Bandela,

As Adam replied- with Year and Month filter, you cannot get last year data. Same issue you will get for next month when you try with 2005 Dec and expect 2006 Jan data.

I would rather create a user prompt on YYYY-MM and try to play with that.

Also, instead of 3 queries to achieve required data, I would go with one query using between condition.

The between condition would work as Month(yyyy-MM) between promptmonth-1 and promptmonth+1.

Let us know your opinion.

-Dhyan

[Adam_Stone]

I wouldn't expect year ends to work properly as you have a prompt on the year being 2005, which will restrict the data returned to only be that year.  The values for December of previous year, or January of the next year would not be in the data that was returned, so how would they be able to be displayed?