Years to continue on x axis in line graph?

[Anonymous]

Hi,

i have data that starts from 2014 capturing the daily registrations of membership (i.e. when a user logs into our site and registers they become a member. so sample data is as follows:-

Year  Month  Day  MemberAdded

2014  Feb     01     1

2014  Feb     01     1

2014  Feb     02     1

2014  Mar     03     1

2014  Mar     04     1

2014  Mar     04     1

I have created a basic line chart but it only shows the months for one year at a time.  I want one year (by month) to flow into the next by the user using the scroll button on the X Axis.  So they can see each month by Year . How can i achieve this?

Cheers,

LC

[Kushal_Chawda]

You can add the Two dimension in line chart, Year & Month. You will be able to see the monthly trend for each year

[tresesco · Accepted solution]

Try creating a calculated dimension like:  =Year & ' ' &Month

[jonathandienst]

Or create a MonthYear derived field and use this as the dimension. When loading:

     LOAD ...

          Year,

          Month,

          Day,

          Date(Date#(Year & Month, 'yyyyMMM'), 'MMM yyyy') as YearMonth,

          ...

Use as dimension and sort numerically.

[Anonymous]

Hi Jonathan,

i tried this but receive the following error:-

Field not found - <Year>

UserSummaryrCalendar: 

Load 

  TempDate AS %UserDateKey, 

  Week(TempDate) As Week, 

  Year(TempDate) As Year, 

  Month(TempDate) As Month, 

  Day(TempDate) As Day, 

  ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

  Date(Date#(Year & Month, 'yyyyMMM'), 'MMMyyyy') as YearMonth,

  WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC

The Calendar script I'm using is:-

QuartersMap: 
MAPPING LOAD  
rowno() as Month, 
'Q' & Ceil (rowno()/3) as Quarter 
AUTOGENERATE (12); 
      
Temp: 
Load 
min(%UserDateKey) as minDate, 
max(%UserDateKey) as maxDate 
Resident UserSummary; 
      
Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
DROP Table Temp; 
      
TempCalendar: 
LOAD 
$(varMinDate) + Iterno()-1 As Num, 
Date($(varMinDate) + IterNo() - 1) as TempDate 
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
      
UserSummaryrCalendar: 
Load 
   TempDate AS %UserDateKey, 
   Week(TempDate) As Week, 
   Year(TempDate) As Year, 
   Month(TempDate) As Month, 
   Day(TempDate) As Day, 
   ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
   Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
   Date(Date#(Year & Month, 'yyyyMMM'), 'MMMyyyy') as YearMonth,
   WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar; 

[Kushal_Chawda]

See the below

QuartersMap: 
MAPPING LOAD  
rowno() as Month, 
'Q' & Ceil (rowno()/3) as Quarter 
AUTOGENERATE (12); 
      
Temp: 
Load 
min(%UserDateKey) as minDate, 
max(%UserDateKey) as maxDate 
Resident UserSummary; 
      
Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
DROP Table Temp; 
      
TempCalendar: 
LOAD 
$(varMinDate) + Iterno()-1 As Num, 
Date($(varMinDate) + IterNo() - 1) as TempDate 
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
      
UserSummaryrCalendar: 
Load 
   TempDate AS %UserDateKey, 
   Week(TempDate) As Week, 
   Year(TempDate) As Year, 
   Month(TempDate) As Month, 
   Day(TempDate) As Day, 
   ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
   Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
  Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'), 'MMMyyyy') as YearMonth,
   WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar;

• Years to continue on x axis in line graph?

[jonathandienst]

You can do the YearMonth or MonthYear directly from TempDate:

     Date(MonthStart(TempDate), 'MMM yyyy') as MonthYear,