10 Replies Latest reply: Aug 12, 2016 3:09 PM by james eckstein

# Only show last 18 months

I am trying to create a line graph with using the expression sum(Cash_on_hand) and the dimension MonthYear.

I need my graph to only show the last 18 months of data, how do I do this.

• ###### Re: Only show last 18 months

May be like this for your expression:

Sum({<Date = {\$("= '>=' & Date(MonthStart(Max(Date), -18), 'DateFieldFormatHere') & '<=' & Date(MonthStart(Max(Date), 0), 'DateFieldFormatHere')"}>}Cash_on_hand)

• ###### Re: Only show last 18 months

is this keeping in mind that my Dimension in MonthYear (a combination of month and year like 08-16)

because it does not seem to be wokring

• ###### Re: Only show last 18 months

Are you using set analysis with date or MonthYear? How is your MonthYear field getting calculated in the script?

• ###### Re: Only show last 18 months

No set analysis in the monthyear. I think that is all i need. it the proper set analysis for my dimension "MonthYear" to only show the Max -18.

• ###### Re: Only show last 18 months

How is MonthYear field created in the script

• ###### Re: Only show last 18 months

MinMax:

Min([Date]) as MinDate,

today()-1 as MaxDate

Resident

[Rev_DD];

Let vMinDate = Num(Peek('MinDate',0,'MinMax'));

Let vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

Let vToday = num(Today());

drop Table MinMax;

//Autogenerate a source table for your calendar

TempCalendar:

\$(vMinDate) + RowNo() - 1 as Num,

Date(\$(vMinDate) + RowNo() - 1) as TempDate

Autogenerate

\$(vMaxDate) - \$(vMinDate) + 1;

//Generate the Master Calendar

MasterCalendar:

Autonumber('|'&Date(TempDate)) as DateNum,

//This Next line determins what links the [Main Data] Table and the [Master Calander] table

Date(Floor(TempDate)) as [Date],

Date(WeekStart(TempDate),'M/D/YYYY') as WeekStart,

Date(WeekStart(TempDate), 'M/D/YY') as WeekStart2,

Week(TempDate) as Week,

Year(TempDate) as Year,

chr(39) & right(Year(TempDate),2) as [Short Year],

Month(TempDate) as Month,

MonthStart(TempDate) as MonthStart,

Year(Floor(TempDate)) &

If(Len(Num(Month(Floor(TempDate))))=1, 0 &

Num(Month(Floor(TempDate))),

Num(Month(Floor(TempDate)))) as Period,

Num(Month(TempDate), '00') as MonthNum,

Day(TempDate) as Day,

WeekDay(TempDate) as WeekDay,

'Q' & ceil(Month(TempDate)/3) as Quarter,

'Q' & ceil(Month(TempDate)/3)&'-'&

Right(Year(TempDate),2) as QuarterYear,

Date(monthstart(TempDate),'MM-YY') as MonthYear,

autonumber(Date(monthstart(TempDate),'MMM-YY')) as MonthYearID,

Week(TempDate) & '-' & Right(Year(TempDate), 2) as WeekYear,

Right(Year(TempDate), 2) & '-' & Week(TempDate) as YearWeek,

autonumber(Right(Year(TempDate), 2) & '-' & Week(TempDate)) as YearWeekID,

inyeartodate(TempDate,\$(vToday),0) * -1 as CurYTDFlag,

inyeartodate(TempDate,\$(vToday),-1) * -1 as LastYTDFlag,

if(date(TempDate) >= Date(AddMonths(Today(0),-12)), '1') as Rolling12Mo,

if(date(TempDate) >= Date(Today(0)-7), '1') as Rolling7Day,

if(date(TempDate) = Date(Today(0)-2), '1') as PreviousDay,

if(date(TempDate) >= Date(Today(0)-90), '1') as RollingAvg

Resident TempCalendar

Order By TempDate ASC;

//Delete temp table

Drop Table TempCalendar;

exit SCRIPT;

• ###### Re: Only show last 18 months

Try this:

Sum({<MonthYear= {\$("= '>=' & Date(MonthStart(Max(MonthYear), -18), 'MM-YY') & '<=' &Date(MonthStart(Max(MonthYear), 0), 'MM-YY')"}>}Cash_on_hand)

• ###### Re: Only show last 18 months

Sunny,

This is not working. I am not getting any values if I use that as my expression

• ###### Re: Only show last 18 months

Sorry, I made some mistakes in the expression. Try now (hopefully no more mistakes)

Sum({<MonthYear= {"\$(='>=' & Date(MonthStart(Max(MonthYear), -18), 'MM-YY') & '<=' & Date(MonthStart(Max(MonthYear), 0), 'MM-YY'))"}>}Cash_on_hand)

• ###### Re: Only show last 18 months

That did the trick!

Thanks Sunny!