Tableau (5)奥迪汽车数据分析案例.
文章目录
-
- 奥迪销售驾驶舱
- 1 准备数据源
- 2 同比及环比
-
- 2.1 准备工作
- 2.2 同比及环比
- 3 车型-销售额分析
- 4 月度销售趋势(近12个月)
- 5 区域销售占比图
- 6 销量Top 3及Bottom 3的车型
-
- 6.1 Top 3
- 6.2 Bottom 3
- 6.3 切换
- 7 销售驾驶舱
- 8 仪表盘的交互
- 9 结果
- 10 Tableau发布
奥迪销售驾驶舱
![在这里插入图片描述](https://img-blog.csdnimg.cn/2019041818131945.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
1 准备数据源
(1)新建文件,并建立数据源。使用MySQL连接数据源,连接数据库,修改名字
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418181506742.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(2)添加“筛选器”
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418181522915.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(3)对筛选出的结果的字段进行重命名
![在这里插入图片描述](https://img-blog.csdnimg.cn/2019041818153739.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(4)要先制作区块地图:所以要先添一个数据源即“5大区域坐标信息.xls”,数据-新建数据源,把“区域编号”改名为“区域名称”
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418181558621.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(5)切换到“工作表”,把“经度”和“纬度”转成“地理角色”,将“经度”和“纬度”分别拖拽到“列”和“行”,将“区域名称”拖拽到“详细信息”,将“坐标序号”拖拽到“详细信息”
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418181616320.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(6)标记选择“多边形”,将“坐标序号”拖拽到“路径”
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418181630987.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(7)数据切换到“区域数据”,然后拖拽“销量”到“颜色”
![在这里插入图片描述](https://img-blog.csdnimg.cn/2019041818164212.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(8)此时发现默认(自动建立)了一个连接:(因为二个数据有公有字段名称)编辑关系:(如果无公有字段名称,就要手动创建连接,如一个是区域,一个是区域名称),保存整个文件,名为“综合实例1_销售驾驶舱页面”
![在这里插入图片描述](https://img-blog.csdnimg.cn/2019041818170030.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
2 同比及环比
2.1 准备工作
(1)新建文件,并建立数据源。使用MySQL连接数据源,连接数据库,修改名字
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418181732826.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(2)各区域销售情况
SELECT
dca.areaname as `区域`,
sum(case when dca.stat_month='201701' then dca.revenue else 0 end) as `销售收入_本月`,
sum(case when dca.stat_month='201601' then dca.revenue else 0 end) as `销售收入_同期`,
sum(case when dca.stat_month='201612' then dca.revenue else 0 end) as `销售收入_上期`,
sum(case when dca.stat_month='201701' then dca.quantity else 0 end) as `销售数量_本月`,
sum(case when dca.stat_month='201601' then dca.quantity else 0 end) as `销售数量_同期`,
sum(case when dca.stat_month='201612' then dca.quantity else 0 end) as `销售数量_上期`
FROM dw_car_areainfo dca
WHERE dca.stat_month in('201701','201701'-100,'201612')
GROUP BY dca.areaname
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418181809110.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(3)各区域销售同比环比
SELECT
main.`区域`,
main.`销售收入_本月`,
round((case when main.`销售收入_同期`<>0 then(main.`销售收入_本月`-main.`销售收入_同期`)/main.`销售收入_同期`*100 else 0 end),2)as `销售收入_同比`,
round((case when main.`销售收入_上期`<>0 then(main.`销售收入_本月`-main.`销售收入_上期`)/main.`销售收入_上期`*100 else 0 end),2)as `销售收入_环比`,
main.`销售数量_本月`,
round((case when main.`销售数量_同期`<>0 then(main.`销售数量_本月`-main.`销售数量_同期`)/main.`销售数量_同期`*100 else 0 end),2)as `销售数量_同比`,
round((case when main.`销售数量_上期`<>0 then(main.`销售数量_本月`-main.`销售数量_上期`)/main.`销售数量_上期`*100 else 0 end),2)as `销售数量_环比`
from
(SELECT
dca.areaname as `区域`,
sum(case when dca.stat_month='201701' then dca.revenue else 0 end) as `销售收入_本月`,
sum(case when dca.stat_month='201601' then dca.revenue else 0 end) as `销售收入_同期`,
sum(case when dca.stat_month='201612' then dca.revenue else 0 end) as `销售收入_上期`,
sum(case when dca.stat_month='201701' then dca.quantity else 0 end) as `销售数量_本月`,
sum(case when dca.stat_month='201601' then dca.quantity else 0 end) as `销售数量_同期`,
sum(case when dca.stat_month='201612' then dca.quantity else 0 end) as `销售数量_上期`
FROM dw_car_areainfo dca
WHERE dca.stat_month in('201701','201701'-100,'201612')
GROUP BY dca.areaname) as main
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418181845807.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
2.2 同比及环比
(1)新建数据源,新建自定义sql,生成同比环比数据
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418181954632.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182000912.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(2)新建工作表,名为“区域表格”,拖拽“区域”到“行”,把“销售收入_同比”,“销售收入_环比”,“销售收入_本月”,“销售数量_本月”,“销售数量_同比”,“销售数量_环比”拖拽到“表格中”
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182021820.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(3)把“同比”及“环比”等修改为“百分比”形式
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182043691.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(4)修改原来自定义SQL
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182059773.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
3 车型-销售额分析
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182130849.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(1)修改上一个自定义SQL
SELECT
车型,
销售数量_本月 as 本月,
round((case when 销售数量_同期<>0 then(销售数量_本月-销售数量_同期)/销售数量_同期 else 0 end),2)as 同比,
round((case when 销售数量_上期<>0 then(销售数量_本月-销售数量_上期)/销售数量_上期 else 0 end),2)as 环比
from
(SELECT
dsm.carclass as 车型,
sum(case when dsm.stat_month='201701' then dsm.quantity else 0
end) as 销售数量_本月,
sum(case when dsm.stat_month='201601' then dsm.quantity else 0
end) as 销售数量_同期,
sum(case when dsm.stat_month='201612' then dsm.quantity else 0
end) as 销售数量_上期
FROM dw_saleinfo_month dsm
WHERE dsm.stat_month in('201701','201701'-100,'201612')
and dsm.brand='奥迪'
GROUP BY dsm.carclass) as main
![在这里插入图片描述](https://img-blog.csdnimg.cn/2019041818222172.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(2)新建数据源
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182234404.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(3)新建工作表名为“车型数据”,把“同比”及“环比”调成“百分比”
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182253963.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182259420.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(4)把“同比”正数为绿色,负数为红色
![把“度量值”拖拽到“颜色”里,点击“颜色”,编辑“色彩”](https://img-
blog.csdnimg.cn/20190418182313230.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
4 月度销售趋势(近12个月)
(数据源dw_saleinfo_month)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182359545.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(1)新建数据源,同时做一下“筛选”,筛选品牌为奥迪的,同时最近12个月的
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182413786.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(2)修改一下字段名,并隐藏不用的字段
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182424542.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(3)新建工作表名为“车型月度趋势”,将“统计月份”拖拽到“列”,将“收入”和“销量”拖拽到“行”,将“销量”改为“折线图”,“收入”改为“柱形图”,在“销售”右击选择“双轴”
![在这里插入图片描述](https://img-blog.csdnimg.cn/2019041818243861.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
5 区域销售占比图
(1)分析奥迪的区域销量
SELECT
dca.areaname as 区域,
dca.quantity as 销量
from dw_car_areainfo dca
where dca.brand='奥迪'
and dca.stat_month='201701'
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182549290.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(2)奥迪的区域销量及所有车型的总销量
SELECT
区域,
销量,
总销量
from
(SELECT
dca.areaname as 区域,
dca.quantity as 销量
from dw_car_areainfo dca
where dca.brand='奥迪'
and dca.stat_month='201701'
) a,(
SELECT
sum(dca.quantity) as 总销量
from dw_car_areainfo dca
where dca.brand='奥迪'
and dca.stat_month='201701') b
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182703802.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(3)奥迪及其他车型区域销量
SELECT
区域,
销量,
(总销量-销量) 其他销量
from
(SELECT
dca.areaname as 区域,
dca.quantity as 销量
from dw_car_areainfo dca
where dca.brand='奥迪'
and dca.stat_month='201701'
) a,(
SELECT
sum(dca.quantity) as 总销量
from dw_car_areainfo dca
where dca.brand='奥迪'
and dca.stat_month='201701') b
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182747579.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(4)新建数据源,新建工作表改名为“区域销量占比图”,“区域”拖拽到“筛选器”中,选择“北部”
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182835180.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(5)标记选择“饼图”,把“度量名称”拖拽到“颜色”,把“度量值”拖拽到“角度”,上图可能是“北部”数据少,所以很不明显,我们可以更改为“西部”
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182847503.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(6)拖拽“记录数”到“行”,计算“平均值”,同样方法再来一次,调整上面那个“大小”,同时编辑轴,固定范围,把第二个的“度量名称”去掉
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182906903.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(7)下面那个固定开始和固定结束也都要上面一样才可以,否则不在一个中心点,把下面那个编辑颜色为“白色”,设置双轴
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418182925160.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
6 销量Top 3及Bottom 3的车型
6.1 Top 3
(1)新建数据源,即dw_saleinfo_month那个表,筛选“品牌”为“奥迪”,及“月份”为“201701”,把数据连接名改为“车型排名数据”,修改字段名称
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418183125497.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(2)新建工作表名为“车型top3”,拖拽“车型”到“行”,“收入”和“销量”拖拽到“表格中”,将“车型”拖拽到“筛选器”
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418183138427.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
6.2 Bottom 3
直接复制工作表“车型TOP3”并改名“车型BOTTOM3”,修改“筛选器”中的“车型”
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418183153370.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418183303823.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
6.3 切换
(1)切换到工作表“车型TOP3”,创建参数
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418183316877.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(2)创建计算字段,名为“Top筛选”,将“Top筛选”拖拽到“筛选器”里
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418183335706.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(3)显示参数控件
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418183347472.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(4)切换到工作表“车型Bottom3”,定义计算字段,名为“Bottom筛选”,将“Bottom筛选”拖拽到“筛选器”
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418183403667.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(5)显示“参数控件”,发现点Bottom就有数据,点TOP没有
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418183414849.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(6)把仪表板原来右下角的删除,用一个空白占一个位:(在对象中),将左侧的工作表“车型top3”拖拽到“空白”,右击车型top3,选择“隐藏标题”
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418183427296.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(7)当右边选择“Bottom”时,就是一片空白了,此时把“车型Bottom3”拖拽到“空白”,然后也“隐藏标题”,也“浮动”,此时就可以切换了
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418183439442.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(8)右边选择参数处也调成”浮动”,把这部分也调整到右下角,隐藏标题:(去掉“显示标题”)
![在这里插入图片描述](https://img-blog.csdnimg.cn/2019041818345186.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
7 销售驾驶舱
新建“仪表盘”改名为“销售驾驶舱”,拖拽“车型数据”工作表到“仪表盘”,在把“车型月度趋势”拖拽到“仪表盘,并且放到“车型数据”下面,将“区域分析”拖拽到“车型数据”的右边,将“区域表格”拖拽到“车型月度趋势”的右边
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418183210853.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
8 仪表盘的交互
1.通过点击“车型数据”中的“车型”来控制“车型月度趋势”的数据
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418183922210.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
2.通过点击“区域分析”中的“区块”,控制右方“区域销量占比”
仪表板操作
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418183951183.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
3.整体加参数进行筛选(如选择年月)
(1)创建参数,名为“选择年月”
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418184018537.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(2)修改“区域表格数据”(自定义SQL)
SELECT
区域,
销售收入_本月,
round((case when 销售收入_同期<>0 then(销售收入_本月-销售收入_同期)/销售收入_同期 else 0 end),2)as 销售收入_同比,
round((case when 销售收入_上期<>0 then(销售收入_本月-销售收入_上期)/销售收入_上期 else 0 end),2)as 销售收入_环比,
销售数量_本月,
round((case when 销售数量_同期<>0 then(销售数量_本月-销售数量_同期)/销售数量_同期 else 0 end),2)as 销售数量_同比,
round((case when 销售数量_上期<>0 then(销售数量_本月-销售数量_上期)/销售数量_上期 else 0 end),2)as 销售数量_环比
from
(SELECT
dca.areaname as 区域,
sum(case when dca.stat_month=<参数.选择月份> then dca.revenue else 0 end) as 销售收入_本月,
sum(case when dca.stat_month=<参数.选择月份>-100 then dca.revenue else 0 end) as 销售收入_同期,
sum(case when dca.stat_month=(case when right(<参数.选择月份>,2)='01' then <参数.选择月份>-89 else <参数.选择月份>-1 end ) then dca.revenue else 0 end) as 销售收入_上期,
sum(case when dca.stat_month=<参数.选择月份> then dca.quantity else 0 end) as 销售数量_本月,
sum(case when dca.stat_month=<参数.选择月份>-100 then dca.quantity else 0 end) as 销售数量_同期,
sum(case when dca.stat_month=(case when right(<参数.选择月份>,2)='01' then <参数.选择月份>-89 else <参数.选择月份>-1 end ) then dca.quantity else 0 end) as 销售数量_上期
FROM dw_car_areainfo dca
WHERE dca.stat_month in(<参数.选择月份>,<参数.选择月份>-100,(case when right(<参数.选择月份>,2)='01' then <参数.选择月份>-89 else <参数.选择月份>-1 end ))
and dca.brand='奥迪'
GROUP BY dca.areaname) as main
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418184103260.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(3)在“区域表格数据”中,”显示参数控件”,选择“区域数据”的数据源,点右上角“编辑”,其他数据源也同理
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418184120332.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(4)在其中的一个表中把“选择年月”参数开放出来
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418184131985.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
9 结果
调整各个工作表的大小及颜色,并布局再仪表盘上
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418184232421.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
10 Tableau发布
(1) https://public.tableau.com/s/ 注册帐号
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418184321756.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(2)登录
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418184340367.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(3)登录Tableau public服务器,弹出提示信息,因为我们连接了动态数据,如EXCEL及MYSQL,所以在发布之前,每个数据要做一个数据提取
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418184358364.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(4)把那几个数据源都要“提取”,然后保存
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418184410758.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)
(5)展示
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190418184434901.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXl1d2Vp,size_16,color_FFFFFF,t_70)