Creating chart for time series data

[#1]

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.

[edg_baltazar · Accepted solution]

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!

Regards!

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

Time_T:

Load

          *,

          Date as Datex,

          Year(Date) AS ,

          Month(Date) AS ,

          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 ,

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

;

Load

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

;

[#3]

Thanks a lot, Edgar. It worked nicely. I just have one question. Is there any way to put ending date of the week instead of week number on the x axis?

[edg_baltazar]

Hello! , yes add this code line to the query that i attach in the past comment ...

WeekEnd(Date) as WeekEnd,

(Below the

LOAD *,

it's ok)

and instead of using week field as dimension use WeekEnd Field!,

Regards!