Forum Discussion
Creating a date table using only months and years
How to create a date table using only month and years? I can't filter my current date by days table because it was created using a formula.
Example:
ID Year Month MonthNumber
201501 2015 January 01
201502 2015 February 02
201503 2015 March 03
etc.
Reason being, to retrieve google analytics data over a long period of time by days (ga:date) it doesn't return more than a couple of months. Though if retrieved by month it appears to retrieve data that goes further back. Though I still need to create relations to a date table, which I cannot do to the regular date table with days since it needs unique values.
Any help much appreciated.
rrekker what about if you create a new column which you can prefix the date.
So as with your example you have the year of 2015 and the MonthNumber of 01, so when you create your new column create it as "01/01/2015" where the first 01 is the first day of the month?
You can then link that to your date table?
3 Replies
You should be able to use any date table, like DateStream, etc. and just create a calculated column to combine your year and month and then use that to create a relationship.
rrekker what about if you create a new column which you can prefix the date.
So as with your example you have the year of 2015 and the MonthNumber of 01, so when you create your new column create it as "01/01/2015" where the first 01 is the first day of the month?
You can then link that to your date table?You are right in that I can get Month of Year from Google Analytics, add a 01 to it and connect this to the unique 01/01/2015 of the regular date table. Smart thinking!
This is the formula i entered in the new column:
Date = RIGHT([Month of Year],2)&"/01/"& LEFT([Month of Year],4)
Though not an exact solution to this question, it's a viable workaround. Cheers!
