Master Calendar and Calculated Element List in Set Analysis

[dena_reavis]

Dear Experts,

I am trying to create a set analysis to calculate the previous month's total, so it can be compared with totals in the month, and calculate other things like change from previous month, etc. But I can't seem to get my calculation right. My master calendar has a field for the age of the month, where the current month is 0 and last month is 1, so on. I am trying to get the month age to increment in the element list, but I do not have this right.

The attached file has some sample data and my calendar. this is what I have:

I am trying to show

I have made many tries and all I seem to get is the same month:

sum({$<MONTH_AGE ={"=$(=max(MONTH_AGE)+1)"} >} Amount)

At first I tried it without the max(MONTH_AGE) because I didn't think I needed it. I'm not sure what it is wrong with my syntax. Please help.

Thank you!!

[dena_reavis]

Hi Sunny,

I was wondering, is there any way to get the August value to calculate using the Before function? It is the first value and I was hoping for a way to get it to calculate even though it would be the July value which falls in the previous fiscal year. Our fiscal year runs August through July.

Thank you,

D

[sunny_talwar]

Created a new MonthYear field in the script and use this expression

Only({<YEAR_AGE = {0}>}Aggr(Above(Sum({$<Group={'1','2','3'}>} Amount), 1), MonthYear))