Ad-hoc free Reports in Qlikview

Ad Hoc Reporting allows end users to easily build their own reports and modify existing ones with little to no training

ad-hoc 网上很多书是写的是特别报告，这边根据报告的特性翻译成自助报告。

ad-hoc 允许最终用户轻松构建自己的报告，并修改现有的报告

youtube视频

1、创建数据表：Sales data

2、新建维度表和事实表：DimensionList；ExpressionList

3、Ad-hoc free report 核心是表可以是垂直表也可以是透视表，重点在点击维度和筛选项的时候展示数据，所以需要维度字段和事实字段的启动条件。

Alt(WildMatch(GetFieldSelections(%Dimensions, '|'),'*City*'),0)

substringcount(If(GetSelectedCount(%Dimensions)=0,'',Concat(DISTINCT %Dimensions,'|')
              ),'City')  

维度
表达式

代码如下

// Load the Sales data
trace  Loading  Sales tbale;
Sales:
LOAD * INLINE [
SalesPerson, Country, City, Product, Sales, Orders
Joe, Germany, Berlin, Bricks, 129765, 399
Joe, Germany, Berlin, Brogues, 303196, 5842
Joe, Germany, Berlin, Widgets, 64358, 1603
Joe, Germany, Berlin, Woggles, 120587, 670
Joe, Germany, Frankfurt, Bricks, 264009, 2327
Joe, Germany, Frankfurt, Brogues, 369565, 3191
Joe, Germany, Frankfurt, Widgets, 387441, 5331
Joe, Germany, Frankfurt, Woggles, 392757, 735
Joe, Germany, Munich, Bricks, 153952, 1937
Joe, Germany, Munich, Brogues, 319644, 645
Joe, Germany, Munich, Widgets, 47616, 2820
Joe, Germany, Munich, Woggles, 105483, 3205
Brian, Japan, Osaka, Bricks, 17086, 281
Brian, Japan, Osaka, Brogues, 339902, 2872
Brian, Japan, Osaka, Widgets, 148935, 1864
Brian, Japan, Osaka, Woggles, 142033, 2085
Brian, Japan, Tokyo, Bricks, 161972, 1707
Brian, Japan, Tokyo, Brogues, 387405, 2992
Brian, Japan, Tokyo, Widgets, 270573, 3212
Brian, Japan, Tokyo, Woggles, 134713, 5522
Brian, Japan, Yokohama, Bricks, 147943, 4595
Brian, Japan, Yokohama, Brogues, 405429, 6844
Brian, Japan, Yokohama, Widgets, 266462, 3158
Brian, Japan, Yokohama, Woggles, 477315, 5802
Joe, UK, Birmingham, Bricks, 23150, 1754
Joe, UK, Birmingham, Brogues, 200568, 1763
Joe, UK, Birmingham, Widgets, 262824, 617
Joe, UK, Birmingham, Woggles, 173118, 5359
Joe, UK, London, Bricks, 621409, 712
Joe, UK, London, Brogues, 504268, 2873
Joe, UK, London, Widgets, 260335, 1313
Joe, UK, London, Woggles, 344435, 743
Joe, UK, Manchester, Bricks, 401928, 1661
Joe, UK, Manchester, Brogues, 7366, 2530
Joe, UK, Manchester, Widgets, 6108, 5106
Joe, UK, Manchester, Woggles, 269611, 4344
Mary, USA, Boston, Bricks, 442658, 3374
Mary, USA, Boston, Brogues, 147127, 3129
Mary, USA, Boston, Widgets, 213802, 1604
Mary, USA, Boston, Woggles, 395072, 1157
Michael, USA, Dallas, Bricks, 499805, 3378
Michael, USA, Dallas, Brogues, 354623, 18
Michael, USA, Dallas, Widgets, 422612, 2130
Michael, USA, Dallas, Woggles, 217603, 2612
Mary, USA, New York, Bricks, 313600, 6468
Mary, USA, New York, Brogues, 559745, 1743
Mary, USA, New York, Widgets, 94558, 2910
Mary, USA, New York, Woggles, 482012, 3173
Michael, USA, San Diego, Bricks, 95594, 4214
Michael, USA, San Diego, Brogues, 24639, 3337
Michael, USA, San Diego, Widgets, 107683, 5257
Michael, USA, San Diego, Woggles, 221065, 5058
];

trace  Loading  Dimension tbale;
// Load the list of dimensions
DimensionList:
Load * Inline [
%Dimensions
SalesPerson
Country
City
Product
];

trace  Loading  Expression tbale;
// Load the list of expressions
ExpressionList:
Load * Inline [
%ExpressionName
Total Sales
Order Count
Avg. Sales
];

最终展现

参考来源

https://www.youtube.com/watch?v=z75R7bThGZY
https://www.jinfonet.com/blog/what-is-ad-hoc-reporting/