本文主要是近期学习Qlik sense的一些记录与总结,内容尚未完善,适合同初学QS的童鞋一同探讨,后续会有补充,若发现文中有不正确之处,烦请联系指正
(一) 数据连接
- 连接SQL Server数据库:Sever处填写“localhost”,database处填写“某个数据库名”,一般SQL Server 默认port是1433(视情况更改),user name:一般情况为sa(视情况更改),密码:“”,其他的配置可以不用填
* 一般在这里想要控制用户表的权限,则在数据库中设置
(二) 常用函数
- 分组函数:添加计算列的时候,比如:想要将价格分档的时候,用class:class([列名],5,‘价格分档’),第一个参数是要分组的列名,第二个参数是指,系统自动判断最低的值,然后按增值5进行分组,第三个是给名字
- 聚合函数:sum()、avg()、count()、min()等函数,参数是列字段,如sum(数量)
注意对于列名,在qliksense中可以直接写上名称,规范的是在名称加上左右中括号[]
- 迭代,QS不像powerbi有sumx()之类的迭代函数,但是对于此类求法可以直接sum(数量*单价),函数中则会先计算行上下文的值再聚合求和
- 求占比:比如要求,某个维度上销售额占总销售额的比值:
sum([数量][价格])/sum(total [数量][价格])
- 在函数内部加筛选条件,类似powerbi 中calculate()函数,QS使用{<>}此类符号来限定筛选条件
# 例如:求产品名称中美式中杯的销售额
sum({<产品名称={"美式中杯"}>} 数量*价格)
等于以下的函数语句
sum(if(产品名称="美式中杯",数量*价格))
# 条件为 **不等于** 的情况
1. 当维度中存在空值时,比如算咖啡种类中,非美式的其他咖啡的销售额,若包含空值,则公式为:sum({<咖啡种类-={"美式"}>} 数量*单价),即直接在条件前面加"-"
2. 当想要求非美式,且不包含空值的情况下的销售额,公式为:sum({<咖啡种类={“*”}-{“美式”}>}数量*单价),当出现通配符字眼时,要用双引号
注意:条件中美式中杯是用双引号括起来,这里用单引号也是没问题的,但是建议以后条件都用双引号括起来,原因是如果条件中出现通配符,比如*时,用但引号将无法识别
- Total,相当于powerbi中的allselected,即可以忽略外部筛选器的作用,单我们求销售额占比时,就会用分维度情况下的销售额除以总的销售额,此时总的销售额,就需要用到total
- all,也是总的意思,只是跟total的区别是:total 求总和会根据外部筛选条件的变化来求,比如销售额占比,维度是咖啡种类,咖啡种类有4类,若此时只选择了两种,则total 只是这两种的和,而all是4类咖啡的总和
# 求各个地市咖啡的销售额占比
sum(数量*价格)/sum(total 数量*价格)
- only()函数,当只有唯一值时,返回该值,大于等于2时,返回null,相当于powerbi中的hasonevalues()
- nullcount(),统计列中空值的数量
- div(x1,x2)除法,mod(x1,x2)求余数,但该函数的x2必须大于0,而fmod(x1,x2)则不用
- fabs取绝对值
- 设定某个图表不与其他图表联动:这个很重要,在qlik中是通过度量值来控制组件间的联动,比如控制销售额不受其他组件的联动,则新建一个度量值:sum({1}数量价格),则只要用了该度量值的组件,都不会受到其他图表联动,但可以筛选其他组件
- 当前筛选条件下的值,用KaTeX parse error: Expected '}', got 'EOF' at end of input: …筛选条件下的销售额:sum({} 数量价格),如果此时想要 求出除了当前筛选项的销售额,则可以:sum({1-$} 数量价格
- 返回上一步操作的值(即回退操作),sum({$1} 数量价格),而相反的,返回前进一步操作的值,sum({$_1}数量*价格)
- QS中,我们在选择维度的时候,系统会默认隐藏掉没有选择到的维度项,若我们要显示所有的维度项sum(<咖啡种类 => }数量*价格),一般用于分析时间趋势的时候用到
-
重点函数:aggr ,高级聚合函数;当在聚合函数内嵌套聚合函数时,一般情况下是报错,但是用aggr则能实现,如:avg(aggr(sum(数量*价格),产品名称)),求解加权平均值
- date#(),强行转化为日期格式,比如有一个日期列,存放的格式为2018/Jan-1,系统无法识别这样的日期格式,这时通过date#(日期,‘YYYY/MMM-D’),便能将日期列转化为标准的日期格式
- num#(),强行将文本转化为数字格式,num(sum(数量*价格),‘00.0’)
(三) 扩展插件(主题或者图表组件)
- 下载地址:https://developer.qlik.com/garden;进入这个网址,然后选择sense,这时进入的页面会列出很多免费的插件,当然插件会根据你版本的更新,兼容性可能出现问题。一般进入网址后,有些可以直接下载到zip整个包,直接解压到单击版或服务器版sense的目录下 ,一般是在extensions的文件夹下面;网站还有插件使用示例的文件为.qvf格式的,解压到apps的文件夹下
(四) 属性相关设置
- 饼图:饼图有时分类太多的情况下,比如原本有10类,我们将其分为5类,则系统会默认将值最小的5类归为一类;设置是在:限制-固定值-5
- 表格:设置数据的条件格式,if(column(4)<0,lightred(),lightgreen()),意思是:指标列的第4列的值如果小于0,则为亮红,否则为亮绿;或者可以colormix1(column(4),lightred(),lightgreen()):意思是:指标列中的第四列,靠近0的值为亮红,靠近1的值为亮绿
# 表格:背景条件颜色设置通用: 改动指标和维度
colormix2(
if(
rangemin(avg(total aggr(sum(数量*价格),[产品名称])),
rangemax(sum(数量价格)-avg(total aggr(sum(数量价格),[产品名称])),-avg(total aggr(sum(数量*价格),[产品名称]))))<0,
-sqrt(-(rangemin(avg(total aggr(sum(数量*价格),[产品名称])),
rangemax(sum(数量价格)-avg(total aggr(sum(数量价格),[产品名称])),-avg(total aggr(sum(数量*价格),[产品名称]))))-0)/
(0--avg(total aggr(sum(价格*数量),[产品名称])))),
sqrt((rangemin(avg(total aggr(sum(价格*数量),[产品名称])),
rangemax(sum(数量价格)-avg(total aggr(sum(价格数量),[产品名称])),-avg(total aggr(sum(价格*数量),[产品名称]))))-0)/
(avg(total aggr(sum(价格*数量),[产品名称]))-0))),
LightRed(),Green(),Yellow())
(五) 变量的相关应用
- topn ,可以设置一个v_topn的变量用于传入what-if的参数,并在属性中设置限制时,传入topn的参数,还能结合variable 组件进行灵活选择topn,实现powerbi中what-if的效果
- variable组件中有个很好的功能,就是可以变换形式,其中有个可以转化为buttons的属性,这可以用来作为更灵活的筛选器,比如需要选择多类别的度量值作为筛选时
(六) 通配符
- 当度量值中设置内部筛选条件时,可以使用通配符进行模糊查找,比如:查询产品名称中带有”小杯”字眼的产品的销售额,公式如下:sum({<产品名称={“小杯”}>}价格数量);这里使用到通配符“”代表任意个字符
- 注意:上面的公司中,条件小杯用了双引号而不是单引号,当使用通配符时, 必须使用双引号才能识别,用单引号报错
(七) 数据脚本使用
- 通过“数据加载编辑器”窗口可以插入脚本,修改脚本等
- 通过添加分段,来更好的规范整理脚本
- 蓝色字体的是关键字,通过在关键字后键入“ctrl+H”可以打开函数帮助窗口,或者点击工具栏中问号标志进入帮助模式
- 可以通过快捷键ctrl+空格键,弹出关键字提示窗口
- 如果要注释多行代码,可使用快捷键ctrl+k;第二种方式是在一段前面写入REM,则注释结束的标志是分号;第三种方式是用/和/将注释的内容括起来
- 将交叉表(二维表)转化为1维表,即逆透视,则在数据加载编辑器中,在生成表的脚本顶部加上语句:
Crosstable(“第一个字段名”(将原始表的多个列名转化成一个字段),“第二个字段名(值)”)
比如:crosstable('month',sales)
- 如果在代码中有先Load 某张表,则下面再写代码Load…… 从哪张表取数,可以用resident <table>
- 定义日期格式 Date#(“date”,’MM/DD/YYYY’)等
- 删除表:drop table “tempdata”(表名)
- 删除列:drop filed name1[name2,……]
- Sense 中默认的join 是指outer join,也能使用left join ,right join ,inner join,但用join是将两张表进行合并后生成一张表,而实际中为了减少内存,可以使用keep,同样是可以实现连接的效果,但是内存中不是生成一张表的方式,可以通过规则去改变源表,同样可以,left keep,right keep ,inner keep 等
- subfield(‘列名’,’(分隔符比如-)’,1(分割后的哪部分数据))
(八) 注意
- 关于度量值或者变量的复用:
i.本来是想着写一个度量值,引用到之前写好的度量值,但复用不了
ii.所以想着用变量来复用,结果变量的值是静态的,不能跟随维度筛选而变化
- QS 后台设置是在链接后面加上/qmc
- 关于系统集成的设置需要打开 Dev Hub