Only show last 18 months

[jeckstein]

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.

Thanks in advance.

[sunny_talwar]

May be like this for your expression:

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

[jeckstein]

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

[sunny_talwar]

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

[jeckstein]

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.

[sunny_talwar]

How is MonthYear field created in the script

[jeckstein]

MinMax:

LOAD

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:

Load

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

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

Autogenerate

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

//Generate the Master Calendar

MasterCalendar:

Load

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;

[sunny_talwar]

Try this:

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

[jeckstein]

Sunny,

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

[sunny_talwar · Accepted solution]

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)