Using set analysis to compare end of month data by product

[#1]

Currently I have end of month data for one full calendar year 2014 and 2015 data will feed automatically. What I want is to create a tables that compares my end of month numbers for a product to the previous month/previous quarter/previous semester/previous year. This way I can see how I am doing on a monthly/quarterly/semester/yearly basis. So ideally I would need 4 tables, one for month, one for quarter, one for semester data and one for yearly data.

I have created a master calendar within my script to show:

LOAD TempDate as Date,
Day(TempDate) as Day,
Week(TempDate) as Week,
Weekday(TempDate) as WeekDay,
Month(TempDate) as Month,
Year(TempDate) as Year,
'Q' & ceil(Month(TempDate) / 3) as Quarter,
Date(MonthStart(TempDate),'MMM-YYYY') as MonthYear,
Week(TempDate) & '-' & Year(TempDate) as WeekYear,
InYearToDate(TempDate, $(vToday), 0) * -1 as CurYTDFlag,
InYearToDate(TempDate, $(vToday), -1) * -1 as LastYTDFlag
Resident TempCal
Order By TempDate ASC;

Now if I have the following data for example:

Date	Product	Balance
12/31/2014	Tran	$350,000
12/31/2014	Time	$150,000
12/31/2014	Loan	$100,000
11/28/2014	Tran	$400,000
11/28/2014	Time	$100,000
11/28/2014	Loan	$150,000
10/31/2014	Tran	$500,000
10/31/2014	Time	$400,000
10/31/2014	Loan	$100,000

I would like to create the tables like:

Selecting December

Product	Current Month	Previous Month	Variance
Tran	$350,000	$400,000	-$50,000
Time	$150,000	$100,000	$50,000
Loan	$100,000	$150,000	-$50,000

Selecting November

Product	Current Month	Previous Month	Variance
Tran	$400,000	$500,000	-$100,000
Time	$100,000	$400,000	-$300,000
Loan	$150,000	$100,000	$50,000

Selecting October

Product	Current Month	Previous Month	Variance
Tran	$500,000	$0	$500,000
Time	$400,000	$0	$400,000
Loan	$100,000	$0	$100,000

And eventually when selecting by Quater, Semester and Year to show the variance current balance, previous period balance and the variance. I have tried many set analysis ideas and creating variables but cant seem to get it to work properly.

[prabhuappu · Accepted solution]

Hi,

Please find the attached application.

For Month,

Previous : Sum({<Year={'$(=Max(Year)-1)'}>}Balance)

Current : Sum({<Year={'$(=Max(Year))'}>}Balance)

For Year,

Previous : Sum({<Month={'$(=monthname(Max(Month)-1))'}>}Balance)

Current : Sum({<Month={'$(=monthname(Max(Month)))'}>}Balance)

For Quarter,

Previous : Sum({<Quarter={'$(vQuarter1)'}, Year={$(vYear1)}>}Balance)

Current : Sum({<Quarter={'$(vQuarter)'}, Year={'$(vYear)'}>}Balance)

vYear	=Max(Year)
vYear1	=if((max(Quarter)-2)<=0,max(Year)-1,max(Year))
vQuarter	=max(Quarter)
vQuarter1	=mod(max(Quarter)-1,4) //'S'&if(mod(Quarter-2,4)=0,4,mod(Quarter-2,4))&'-'&if((Quarter-2)<=0,Year-1,Year)

Hope it helps...

Regards,

Prabhu Appu

[#3]

Hi Prabhu,

Should I be creating the variables within my script? or should I do it within the document properties and add a new variable? When I placed it in my script, it gave me a script line error...

Sincerely,

Maria

[prabhuappu]

Hi,

You need to create it either in document properties or variable Overview.

Regards,

Prabhu Appu

[#5]

Thank you Prabhu.

I was able to get it to work, but now the problem with my data is that the month field comes with the month and year. Due to this, it messes up the look and field of the month select field. Is there anyway that I can do this, but eliminating the year from the month field?

Thanks for all your help!!

Sincerely,

Maria

[#6]

Hi,

You can split the filed by using the subfield function ex:

subfield(YearMonth,'-' , 1) as Year

subfield(YearMonth,'-' , 2) as Month

etc

[prabhuappu]

Hi,

As a best practice you can keep both and Month(Jan), Year and MonthName (Jan-2014) in you common Calendar. So that you can use those where ever needed.

My suggestion is to Create another field for month and use it in List box.

Regards,

Prabhu Appu

[#8]

Hi Pabhu,

I tried creating another field for the month within my master calendar already but when I select the month, the numbers dont adjust. Only when I select the month-year within the monthname field is when the numbers adjust in the current and previous tables you helped me create. This is what I created:

MonthName(TempDate) as Month,
Month(TempDate) as Month_SelectField,

Do I have to change the formulas in the tables to include the month field and not the monthname field?

Thanks!

Maria

[#9]

NVM Pabhu. I was able to figure it out. Thank you again for all the help! I appreciate the support

[#10]

Thank you Hampus. I was able to figure it out by creating an additional field for just the month name. I appreciate the help.