Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
May be like this for your expression:
Sum({<Date = {$("= '>=' & Date(MonthStart(Max(Date), -18), 'DateFieldFormatHere') & '<=' & Date(MonthStart(Max(Date), 0), 'DateFieldFormatHere')"}>}Cash_on_hand)
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
Are you using set analysis with date or MonthYear? How is your MonthYear field getting calculated in the script?
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.
How is MonthYear field created in the script
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;
Try this:
Sum({<MonthYear= {$("= '>=' & Date(MonthStart(Max(MonthYear), -18), 'MM-YY') & '<=' &Date(MonthStart(Max(MonthYear), 0), 'MM-YY')"}>}Cash_on_hand)
Sunny,
This is not working. I am not getting any values if I use that as my expression
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)