小智常见报表示例–层次坐标–跨层累计报表
概述
跨层累计:按照从上到下的顺序求累加和。
应用场景
如下图所示,对商品、年份进行跨层累计计算
实现思路
通过下列公式实现跨层累计计算
=IF(&C2 > 1, D2 + E2[C2:-1], D2 + E2[B2:-1,C2:!-1])
示例说明
数据准备
在数据面板中添加数据集,可选择Json数据集和Http接口数据集,Json数据如下图所示:
[{"商品":"A产品","年份":2020,"季度":1,"销售额":5890.56},{"商品":"A产品","年份":2020,"季度":2,"销售额":4566.26},{"商品":"A产品","年份":2020,"季度":3,"销售额":7861.56},{"商品":"A产品","年份":2020,"季度":4,"销售额":5654.56},{"商品":"A产品","年份":2021,"季度":1,"销售额":5820.96},{"商品":"A产品","年份":2021,"季度":2,"销售额":5666.26},{"商品":"A产品","年份":2021,"季度":3,"销售额":8661.56},{"商品":"A产品","年份":2021,"季度":4,"销售额":5254.56},{"商品":"B产品","年份":2020,"季度":1,"销售额":5890.56},{"商品":"B产品","年份":2020,"季度":2,"销售额":4456.16},{"商品":"B产品","年份":2020,"季度":3,"销售额":7561.56},{"商品":"B产品","年份":2020,"季度":4,"销售额":3554.56},{"商品":"B产品","年份":2021,"季度":1,"销售额":4620.96},{"商品":"B产品","年份":2021,"季度":2,"销售额":5456.26},{"商品":"B产品","年份":2021,"季度":3,"销售额":8891.56},{"商品":"B产品","年份":2021,"季度":4,"销售额":5623}]
报表设计
将数据字段依次对应拖拽到对应单元格中,并将A2、B2、C2单元格的表格设置为分组,并把填入的单元格框选后添加分组。如下图所示:
跨层累计的公式说明:
IF(&C2 > 1, D2 + E2[C2:-1], D2 + E2[B2:-1,C2:!-1])
IF(A,B,C):为条件表达式,若A为真,则返回B,否则返回C。
&C2>1:表示判断C2单元格中数值是否大于1,若大于1,则返回D2 + E2[C2:-1],否则返回D2 + E2[B2:-1,C2:!-1];
E2[C2:-1]:表示上一次累计结果,即上一个 C2(上季度)对应的 E2 的值;
E2[B2:-1,C2:!-1]:表示上一组最后一个累计结果,即上一个 B2(上一年)中 C2 扩展出来的最后一个对应的 E2 的值。
效果预览
跨层累计演示.json
https://www.yuque.com/attachments/yuque/0/2023/json/25420404/1686882747761-afc56230-0abf-440c-b230-1da0d3cf8392.json
参考文档
- 小智报表样例源码下载
- https://gitee.com/tizdata_admin/vue-xzreport-example.git
- 小智报表demo体验
- https://xzdemo.tizdata.com/#/imbedShow/reportExample
- 丰富的报表模板示例
- 小智报表在线试用
- https://xzreport.com
- 小智报表js sdk下载
- https://tizdata.com/xzdownload/
- 开发使用文档
- https://doc.tizdata.com/xiaozhi/650