10 Replies Latest reply: Aug 12, 2016 3:09 PM by James Eckstein RSS

    Only show last 18 months

    James Eckstein

      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.

        • Re: Only show last 18 months
          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)

            • Re: Only show last 18 months
              James Eckstein

              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
                  Sunny Talwar

                  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
                      James Eckstein

                      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
                          Sunny Talwar

                          How is MonthYear field created in the script

                            • Re: Only show last 18 months
                              James Eckstein



                              Min([Date]) as MinDate,

                              today()-1 as MaxDate







                              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



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

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


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





                              //Generate the Master Calendar



                              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)))) 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;