Calculated Field 计算入门★★★
考点 2.3.5 Create a calcualted field (e.g. string, date,simple artihmetic)
在开始基础日期函数之前, 我推荐你再复习一下基础基础基础那一张当中, 关于日期的这一课.
日期函数的作用
跟前边的数值计算函数不一样, 数值函数的输入是数值输出也是数值, 所以往往应用起来往往比较简单. 而日期不一样, 日期有很多独特的性质,日期函数往往是围绕着这些独特的性质进行计算. 而且日期函数的输出有时候并不一定是日期, 有时候输入也不一定是日期, 所以在使用日期函数的时候, 必须非常明确你当前所使用的这个日期函数的目的是什么. 或者说这个日期函数到底是干什么的.
我把日期函数的作用分为三种:
1. 创建日期: 把其他形式的数据转化为日期. 比如数字,字符串等等转换成一个日期
2. 定位日期: 定位函数的作用是具体定位一个日期. 比如从今天往前数前三十天, 去年年底的一号, 今年二月份的月底等等.
3. 解析日期: 在得到一个日期之后, 去判断这个日期的年份是多少, 月份是多少, 这一天是星期几等等
这三种函数之间, 最重要的是第二种作用, 其次第三种. 创建日期一般用的最少.
创建一个日期
TODAY( )函数★★★
Today就是今天的意思, 顾名思义Today( )函数返回的就是今天的日期. 在Tableau Dashboard打开的时候, Today( )就是你打开这个报告的日期.
关于Today函数, 你唯一需要记住的是当你把你的Dashboard发布到Tableau Server之后, Today( )是以Tableau Server服务器所使用的那个系统时间来定义的. 如果你在中国打开一个服务器在美国的Tableau Dashboard,那么显示的Today是以美国时间为准的, 即使这个报告是你在中国发布的.
NOW( )函数★
Now是现在的意思, NOW()函数所返回的是此时此刻的日期+时间. 2024-01-01 20:00:00
MAKEDATE( )函数: 通过数字创建日期★
如果我的数据里是时间是分为三个column存在的, Year, Month, Day 而这个三个column当中又都是数字的话, 你就需要使用MAKEDATE( )函数来创建日期.
MAKEDATE(年份数字, 月份数字, 日期数字) → 日期
MAKEDATE(2024,3,13) = 2024-03-13 我在这强调一下, 如果你的数据里月份是带0的话, 03不是一个数字, 这是一个字符, 你必须把字符先转化成数字才行
MAKEDATETIME( )函数★
MAKEDATETIME从名字上和MAKEDATE()很类似, 就是创建一个日期加时间. 但是MAKEDATETIME需要的是一个DATE加上一个时间, 而不是数字, 这点非常重要
MAKEDATETIME(#2012-11-12#, #07:59:00#) → 2012-11-12 07:59:00
解析日期
如果说前边是硬搓出一个日期的话, 那么这个部分主要是根据字符转换日期. 这个函数只有一个, 那就是DATEPARSE( )
DATEPARSE(format, string)
DATEPARSE函数一共有两个输入, 第一个是格式参数, 第二个是要转换的字符串. Tableau官方给出的例子就是
DATEPARSE(“dd.MMM.yyyy“, “15.April.2004“) = 2004-04-15 12:00:00
你能转换的格式千变万化, 因此这个格式参数也是各式各样. 你刚开始学不要把时间浪费在各种格式参数上, 只要知道有这么个函数就行了. 以后用上了再说.
定位日期★★★
定位日期的意思就是我想根据一个基准日期定位另一个日期的时间. 这个'另一个日期'是根据你的基准时间动态变化的.
比如你一个最基本, 去年的今天. 基准日期就是今天, 另一个日期就是去年的今天.
所有时间段或者说Time Interval的计算本质上都是定位日期的计算, 你知道了今天, 知道了去年的今天, 你自然就会计算过去一年包含哪些天了.
常见的定位日期往往包含下边这些术语:
MTD: Month to Date 从本月的一号到今天 → 根据今天计算出本月的一号是哪一天 → 日期>=本月一号 and 日期<=Today( )
YTD: Year to Date 从今年的一月一号到今天 → 根据今天计算出今年的一月一号是哪一天 → 日期>=今年一月一号 and 日期<=Today( )
PMTD: Previous Year Month to Date 从去年的本月一号到去年的今天 → 根据今天计算去年的今天是哪一天 →根据去年的今天去推算去年的今天所在那个月的一月一号→ 日期>=去年的本月一号 and 日期<=去年的今天
当来到PMTD的时候, 你就可以看到需求就已经变的稍微复杂了.
头号重要日期定位函数 DATETRUNC( )★★★
计算月初和年初函数: DATETRUNC(date_part, date)
为啥这个函数这么重要? 我先问你一个问题, 光从这个函数名你能推断出这个函数是干什么的吗? 你猜不到吧, 所以你翻译不过来就说明你记不住. 第二, 这个函数的功能非常常用.
一个经常用但是你又很可能记不住的函数就是非常重要的函数.
首先我们来翻译函数名 DATETRUNC. 这个函数名是DATE加TRUNC. TRUNC是单词Truncate的缩写. Truncate这个单词的含义是截断, 缩短的意思.
如果我们显示一个字符串'1234….' 1234后边的…就表示有数字没显示出来, 这时候我们就说这个数字是Truncated, 被截断了, 没全显示. 但是放在日期这里, 它的含义就是
在1号就把日期截断. 如果我们date_part部分输入的是Month, 那就是截断到这个月的一号, 如果输入的是Year, 那就是截断到这一年的一月一号.
DATRTRUNC('year', #2024-08-30#) 返回的就是2024年的一月一号: 2024-01-01
DATRTRUNC('month', #2024-08-30#)返回的就是2024年8月份的一月一号: 2024-08-01
但是到这这个函数还没有讲完. 如果你最后只记住了这个函数用来计算月初和年初, 那你只得到这个函数的一半真传. 这个函数的另一半真传就是:
DATETRUNC( )同样用来计算年末和月末. Tableau中并没有提供计算月末和年末的函数, 所以你必须月初和年初来计算月末和年末, 那么怎么算?
记住: 你今年的年末就是明年的年初减一天, 你这个月的月末就是下个月的月初减一天, 你想明白了吗?
DATEADD函数 日期加减
DATEADD(datepart, interval, date)
DATEADD就是在Date的基础上+Interval * datepart这么多的时间. DATEADD(‘month‘, 3, #2004-04-15#) → 在2024-04-15 + 3xMonths → 2004-07-15
DATEADD函数唯一需要额外记住的是Interval可不是只能输入正数, 也是可以输入负数的.
DATEADD(‘month‘, -3, #2004-04-15#) 相当于在2004-04-15号的基础上再减三个月, 返回的是2004年1月15号
DATEDIFF函数 日期差函数
DATEDIFF(date_part, StartDate, EndDate)
这个函数就是一个功能, 计算两个日期之间差几天/月/年.
这个函数最容易搞错的就是StartDate和EndDate的位置. 因为在我们的脑海中, 如果你想计算两个日期之间差几天, 你的直觉就是End Date - Start Date
在这个里边你的直觉告诉你 End Date是应该在前边的, 但是DATEDIFF函数不是, Start Date是在前边, End Date是在后边的. 这个顺序是反直觉的