Month on Month Varance..

[sravan_v007]

Hi All,

I am trying get variance between selected month vs previous month. For this I have assigned MonthID for month, created 2 variables i.e. for CurrentMonth and CurrentMonth -1 and called in Set Analysis.

Just to test I have taken it in text object:

=sum({<MonthID={$(vMonthID)}>}Actuals)& ' - '&sum({<MonthID={$(vMonthID_1)}>}Actuals)

But when ever I select Month Name from Month field, it's giving the Actuals for selected month but not for previous month.

If I select the Month from MonthID field, that's giving me the correct values i.e. Current Actuals - Previous Actuals.

On Selection of Month Field:

On Selection of MonthID:

Need help to show the variance based on Month Name selections.

Note: My Financial Calendar Starts from Oct to Sep. So, MonthID for Nov as 2.

Sample:

Date:
LOAD * INLINE [
ANZ_CalenderDate, Actuals
01/01/2017, 1000, A
02/03/2017, 2000, B
03/02/2017, 3000, A
04/06/2017, 4000, B
  ];

Final:
LOAD
ANZ_CalenderDate,
Month(ANZ_CalenderDate) as Month,
AutoNumber(ANZ_CalenderDate) as MonthID,
Year(ANZ_CalenderDate) as Year,
Actuals
Resident Date;
DROP Table Date;

Variables:

Regards,

Sravan.V

[jagan · Accepted solution]

Hi,

Try like this

=sum({<Month_Name=, MonthID={$(vMonthID)}>}Actuals) & ' - '& sum({<Month_Name=, MonthID={$(vMonthID_1)}>}Actuals)

Regards,

jagan.