最近做的Tableau报告里遇到一个非常有意思的复杂组合计算。
问题描述
首先,来说一下业务逻辑。售后服务部门有两种服务业务,第一是免费的保修服务,第二是收费的保外服务。
保修服务是指在保修期内,非客户原因造成的损坏,则可以提供免费维修。
保外服务是指在保修期内因客户原因造成的损坏,和保修期外一切的损坏,提供有偿维修服务。
因此,
-
保内返修率计算公式: 保内维修量 / 保修期内产品量
所有保修维修全部来自于保修期内产品,因此分母为保修期内产品量。 -
保外返修率计算公式:保外维修量 / (保修期内产品量+保修期外的产品量)
保外维修可能来自于保修期外产品量,也可能来自于保修期内(如产品损坏是客户自己造成的),因此分母是保修期内产品量加上保修期外的产品量。 -
总返修率 :(保内维修量+保外维修量) / (保修期内产品量+保修期外的产品量)
总返修率是总返修量除以保修期内加保修期外总产品量。
数据格式为:
产品 | 保修状态 | 维修量 | 产品保有量 |
---|---|---|---|
产品 A | 保内 | 80 | 4500 |
产品 A | 保外 | 180 | 8600 |
产品 B | 保内 | 36 | 500 |
产品 B | 保外 | 78 | 3400 |
产品 C | 保内 | 160 | 4500 |
产品 C | 保外 | 210 | 6700 |
产品A的保内返修率 = 保内维修量 / 保修期内产品量 = 80 / 4500 = 1.78%
产品A的保外返修率 = 保外维修量 / (保修期内产品量+保修期外的产品量) = 180 / (4500 + 8600) = 1.37%
产品A的总返修率 = (保内维修量+保外维修量) / (保修期内产品量+保修期外的产品量)= (80+180) / (4500 + 8600) = 1.98%
实现方法
使用Tableau时你会发现,这个问题不能简单的用聚合公式来计算。我的解决方案如下:
定义一个变量 Failure Rate Type, 数据类型是String,值有三种: INW, OOW, TOTAL. 分别对于现实值 “保修内返修率”,“保修外返修率”, ‘“总返修率’。
定义新字段**[计算 - 维修量]**的计算公式如下:
IF [Failure Rate Type] = 'INW' AND [保修状态] = '保内' THEN [维修量]
ELSEIF [Type] = 'OOW' AND [保修状态] = '保外' THEN [维修量]
ELSEIF [Type] = 'TOTAL' THEN [维修量]
ELSE 0
END
定义新字段**[计算 - 产品保有量]**的计算公式如下:
IF [Failure Rate Type] = 'INW' AND [保修状态] = '保内' THEN [产品保有量]
ELSEIF [Type] = 'OOW' AND ([保修状态] = '保内' OR [保修状态] = '保外') THEN [产品保有量]
ELSEIF [Type] = 'TOTAL' THEN [产品保有量]
ELSE 0
END
这两个字段会根据[Failure Rate Type]的不同选择不同的维修量值和产品保有量值。现在,我们只要定义**[返修率] = sum([维修量])/sum( [计算产品保有量] )**,并且设置“Show Parameter Control” 就可以使用[Failure Rate Type] 控制来得到我们想要的结果了, 即使是使用聚合运算也没有问题。
- 当[Failure Rate Type] = ‘INW’时, 这两个字段以及**[返修率]**呈现出来的结果,以及按产品各级聚合结果如下。
产品 | 保修状态 | 维修量 | 产品保有量 | 计算 - 维修量 | 计算-产品保有量 | 返修率 |
---|---|---|---|---|---|---|
产品 A | 保内 | 80 | 4500 | 80 | 4500 | =80/4500 |
产品 A | 保外 | 180 | 8600 | 0 | 0 | - |
产品 A 总共 | * | 360 | 13100 | 80 | 4500 | = 80/4500 |
产品 B | 保内 | 36 | 500 | 36 | 500 | = 36/500 |
产品 B | 保外 | 78 | 3400 | 0 | 0 | - |
产品 B 总共 | * | 114 | 3900 | 36 | 500 | = 36/500 |
产品 C | 保内 | 160 | 4500 | 160 | 4500 | = 160/4500 |
产品 C | 保外 | 210 | 6700 | 0 | 0 | - |
产品 C 总共 | * | 370 | 11200 | 160 | 4500 | = 160/4500 |
总共 | * | 844 | 28200 | 276 | 9500 | = 276/9500 |
- 而当[Failure Rate Type] = ‘OOW’时, 这两个字段以及**[返修率]**呈现出来的结果如下。
产品 | 保修状态 | 维修量 | 产品保有量 | 计算 - 维修量 | 计算-产品保有量 | 返修率 |
---|---|---|---|---|---|---|
产品 A | 保内 | 80 | 4500 | 0 | 4500 | - |
产品 A | 保外 | 180 | 8600 | 180 | 8600 | =180/8600 |
产品 A 总共 | * | 360 | 13100 | 180 | 13100 | =180/13100 |
产品 B | 保内 | 36 | 500 | 0 | 500 | - |
产品 B | 保外 | 78 | 3400 | 78 | 3400 | =78/3400 |
产品 B 总共 | * | 114 | 3900 | 78 | 3900 | =78/3900 |
产品 C | 保内 | 160 | 4500 | 0 | 4500 | - |
产品 C | 保外 | 210 | 6700 | 210 | 6700 | =210/6700 |
产品 C 总共 | * | 370 | 11200 | 210 | 11200 | =210/11200 |
总共 | * | 844 | 28200 | 468 | 28200 | = 468/28200 |
- 当[Failure Rate Type] = ‘OOW’时, 这两个字段以及**[返修率]**呈现出来的结果如下。
产品 | 保修状态 | 维修量 | 产品保有量 | 计算 - 维修量 | 计算-产品保有量 | 返修率 |
---|---|---|---|---|---|---|
产品 A | 保内 | 80 | 4500 | 80 | 4500 | =80/4500 |
产品 A | 保外 | 180 | 8600 | 180 | 8600 | =180/8600 |
产品 A 总共 | * | 360 | 13100 | 360 | 13100 | =360 /13100 |
产品 B | 保内 | 36 | 500 | 36 | 500 | = 36/500 |
产品 B | 保外 | 78 | 3400 | 78 | 3400 | =78/3400 |
产品 B 总共 | * | 114 | 3900 | 114 | 3900 | =114/3900 |
产品 C | 保内 | 160 | 4500 | 160 | 4500 | = 160/4500 |
产品 C | 保外 | 210 | 6700 | 210 | 6700 | =210/6700 |
产品 C 总共 | * | 370 | 11200 | 370 | 11200 | =370 /11200 |
总共 | * | 844 | 28200 | 844 | 28200 | = 844 /28200 |