观远 BI

灵活日期筛选控制时间区间显示

创建于 2022-11-01 / 最近更新于 2023-12-25 / 3344
字体: [默认] [大] [更大]

需求背景

同一个页面下,只需要一个日期条件,但是控制的卡片需要显示所选日期近7 天、日期所在月10~25号日、最近12个月、最近3年等多种时间区间范围数据。

例如筛选日期2019-10-17,要看卡片时间范围为:2019-10-11~2019-10-17共 7 天、2019-10-10~2019-10-25、2018-11~2019-10共12个月、2017~2019 三年的数据。可视化效果如下。

image.png

新增场景五:筛选日期 2019-10-17,显示 2019-10-01~2019-10-17 每天销售额、2019-10-01~ 2019-10-16 累计销售额(不统计2019-10-17当天)。

场景一:显示选择日期近7 天数据(可指定日期范围)

方法步骤:

(1)设置一个日期全局参数;

(2)卡片内新建1个计算字段,计算出每一行日期与对应传入的参数日期的间隔天数,通过差值进行自由范围控制,如下图;

image.png

(3)在卡片的筛选中,将上一步新建的计算字段拖入,然后设置控制区间;

image.png

(4)添加一个外部的时间筛选器,关联条件设置为关联卡片内使用的日期全局参数;或者新建参数筛选器,选择卡片内使用的日期全局参数。

image.png

场景二:显示选择日期所在月固定区间内数据

方法步骤:

(1)设置一个日期全局参数;

(2)卡片内增加3个计算字段,分别用来控制年月日的筛选,如下图;

image.png

原理是计算出每一行日期,与对应传入的参数日期年月日的差值,通过差值进行自由范围控制

(3)在卡片的筛选中,将控制年月日的三个计算字段拖入,然后设置控制区间,日控制如下图,年、月分别设置为等于0;

image.png

(4)添加一个外部的时间筛选器,关联条件设置为关联卡片内使用的日期全局参数;或者新建参数筛选器,选择卡片内使用的日期全局参数。

场景三:显示选择日期近12个月数据

方法步骤:

(1)设置一个日期全局参数;

(2)卡片内增加2个计算字段,将传入日期参数的年月抽取出来;

* 参数月:month([DYNAMIC_PARAMS.日期])

* 参数年:year([DYNAMIC_PARAMS.日期])

image.png

(3)这一步很重要:原理是通过_开始年月<=数据集年月<=结束年月_的方式控制筛选范围,控制原理是互相做差即_开始年月-数据集年月<=0_的数据在需要显示的范围内,数据集_年月-结束年月_同样,这里结束年月其实就是参数年月。

操作为:将数据集年月做控制计算前处理,新增3个计算字段如下:

* 数据集年月:year([日期字段])*100+month([日期字段])

* 开始年月:case when [参数月] =12 then [参数年]*100+1 when [参数月]<12 then ([参数年]-1)*100+[参数月]+1 end【逻辑:参数月为12月,就取当年1月;非 12 月,就取上一年选择月+1】

* 结束年月:[参数年]*100+[参数月]

image.png

之所以要通过数值计算而不是直接用年月拼接的数值concat(),是因为年月拼接的单数月1月等处理后是20191而不是201901,会和双数月10月201910的计算出错,所以要这么处理

(4)增加2个字段,对年月做差:

* 开始控制:[开始年月]-[数据集年月]

* 结束控制:[数据集年月]-[结束年月]

image.png

(5)将(4)中的两个字段放到卡片的筛选中,控制范围都为<=0;

image.png

(6)添加一个外部的时间筛选器,关联条件设置为关联卡片内使用的日期全局参数;或者新建参数筛选器,选择卡片内使用的日期全局参数。


注意:以上公式是基于抽取数据集(即spark函数)实现,如果是直连数据集,需要换成对应的数据库函数语法。

例如 ,在oracle直连数据集直接新建计算字段,卡片会报错 ORA-00904: "YEAR": invalid identifier。

原因是oracle不支持直接使用year、month函数取日期年份和月份。需要要改成 EXTRACT(MONTH FROM TO_DATE([DYNAMIC_PARAMS.日期],'DD')) 和 EXTRACT(MONTH FROM TO_DATE([DYNAMIC_PARAMS.日期],'DD'))


场景四:显示选择日期近 3 年数据

方法步骤

请完全参照场景三步骤新建字段,根据需求参考以下调整:

* 开始年月:([参数年]-2)*100+1

* 结束年月:[参数年]*100+[参数月]  (只到参数所在年月)

[参数年]*100+12   (参数所在年整年)

image.png

场景五:显示选择日期当天销售额、月初至昨天累计销售额

方法步骤:

1. 设置一个日期全局参数;
2. 卡片内增加3个计算字段,分别用来控制年月日的筛选,如下图;

image.png


原理是计算出每一行日期,与对应传入的参数日期年月日的差值,通过差值进行自由范围控制。
3. 在卡片的筛选中,将控制年月日的三个计算字段拖入,然后设置控制区间,日控制为大于等于 0(如下图),年控制、月控制分别设置为等于0。这么做可以只筛选出当月 1 号至当日的数据,不显示当日以后的数据。

image.png

4. 新建字段“月初至昨日累计金额”使用组合函数来计算月初至昨日累计金额。

条件函数 case when DAYOFMONTH([huikuan_date]) between 1 and DAYOFMONTH([DYNAMIC_PARAMS.日期])-1 then [huikuan_price]
else 0 end
可以筛选出月初至昨日的日销售明细,窗口函数 sum( ) over(partition by month([huikuan_date]) order by [huikuan_date]) 可以对月初至昨日的日销售明细进行累计计算。如果需要以更多维度来分组统计,可以按照顺序把维度字段放到 partition by 后面。累计计算具体使用方法请参考
[如何计算出月累计销售金额]

image.png

5. 把日期字段拖到维度栏,把数据集里的销售金额字段拖到数值栏,聚合方式选“求和”,重命名为当日销售额;再把上一步新建好的字段“月初至昨日累计金额”也拖到数值栏,聚合方式选无处理。

注意:如果维度栏不放日期字段,那么“当日销售额”字段“求和”统计到的其实是月初至当日的累计销售额,而不是当日销售额,这时需要新建一个字段 case when [huikuan_date]=[DYNAMIC_PARAMS.日期] then [huikuan_price]
else 0 end
并拖入数值栏来统计当日销售额,聚合方式选“求和”。两种方式效果对比如下图。

image.png


6. 添加一个外部的时间筛选器,关联条件设置为关联卡片内使用的日期全局参数;或者新建参数筛选器,选择卡片内使用的日期全局参数

注意:以上所有计算字段使用的是系统内置 Spark SQL 函数,如卡片使用直连数据集,请使用对应数据库函数。

案例

Demo体验

登录体验案例Demo自定义快捷日期区间

案例下载

观远应用市场下载本案例到本地环境动态时间宏

0 人点赞过