3 Replies Latest reply: Jun 28, 2012 12:39 PM by Edgar Baltazar RSS

    Creating chart for time series data

      I have daily time series data for sales (actual and goals). I would like to plot weekely sales over time. How can i do that? Please guide me to appropreate help or tutorial.

        • Re: Creating chart for time series data
          Edgar Baltazar

          First of all you need to have  a calendar table in you datamodel (this table contains date, week, month, monthyear year... fields) and you have to relate your field data of the sales table with the date of calendar table... then when you create your chart in the dimension tab you only have to use de week field... and thats all!


          Check that the date fields have the same format in both tables!


          Here it's a simple script to create a calendar table  ... let me know if this help you!





          Let EndDate =   num(date(YearEnd  (AddMonths(Today(),0,0),1),'YYYYMMDD'));

          Let StartDate = num(makedate(2008,1,1));

          Let CurrentDate=num(date(Today(),'YYYYMMDD'));

          Let vCurrentYear=Year(Today());

          Let LY_YTD=num(AddMonths(Today(),-12,0));

          Let DaysToEnd=Monthend(Today())-Today();

          Let Timespan=Year(Today())-Year(Date($(StartDate)));






                    Date as Datex,

                    Year(Date) AS [Y],

                    Month(Date) AS [M],

                    num(Month(Date)) AS [MonthNum],

                    num(Week(Date),00) AS [Week],

                    Day(Date) AS [Day],

                    WeekDay(Date) AS [WeekDay],

                    Year(Date)&num(Month(Date),00) AS [Year Month2],

                    if(Date<=$(CurrentDate),Date) AS Date2,// Dates until Current date, used for balance dates

                    Date(Monthstart(Date),'MMM-YY') AS [RollMonth],

                    Year(Date)&num(Month(Date),00) AS [Year Month],

                    Dual(WeekYear(Date)&'W'&num(Week(Date),00),WeekYear(Date)&num(Week(Date),00)) AS [Year Week2],

                    Year(Date)&num(Week(Date),00) AS [Year Week],

                    Dual('T'&Num(Ceil(Num(Month(Date))/3)),Num(Ceil(NUM(Month(Date))/3),00)) AS [T],

                    Year(Date)&Dual('Q'&Num(Ceil(Num(Month(Date))/3)),Num(Ceil(NUM(Month(Date))/3),00)) AS [Year Quarter]





                     Date($(StartDate)+(Iterno()-1),'YYYYMMDD') AS Date

                     // Date, to be used as key feild to transactions


          Autogenerate 1

          While Date($(StartDate)+(Iterno()-1)) <=Date($(EndDate))