Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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.

Tags (3)
1 Solution

Accepted Solutions
edg_baltazar
Not applicable

Re: Creating chart for time series data

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 Replies
edg_baltazar
Not applicable

Re: Creating chart for time series data

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

;

Not applicable

Re: Creating chart for time series data

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
Not applicable

Re: Creating chart for time series data

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!