QV函数指南.doc

目录1基本统计函数101.1SUM函数101.2MIN函数101.3MAX函数111.4Only函数111.5Mode函数111.6firstsortedvalue函数112字符串统计函数122.1MinString函数122.2MaxString函数122.3Concat函数123计数统计函数133.1Count函数133.2NumericCount函数133.3TextCount函数143.4NullCount函数143.5MissingCount函数154统计函数154.1Avg函数154.2stdev函数154.3median函数164.4fractile函数164.5skew函数164.6kurtosis函数175财务统计函数285.1Irr函数285.2Xirr函数305.3Npv函数305.4Xnpv函数316统计测试函数326.1chi2test_p函数326.2chi2test_df函数336.3chi2test_chi2函数346.4TTest_t函数346.5TTest_df函数356.6TTest_sig函数356.7TTest_dif函数366.8TTest_sterr函数366.9TTest_conf函数376.10TTest_lower函数376.11TTest_upper函数386.12TTestw_t函数386.13TTestw_df函数396.14TTestw_sig函数396.15TTestw_dif函数406.16TTestw_sterr函数406.17TTestw_conf函数416.18TTestw_lower函数416.19TTestw_upper函数426.20TTest1_t函数426.21TTest1_df函数436.22TTest1_sig函数436.23TTest1_dif函数436.24TTest1_sterr函数446.25TTest1_conf函数446.26TTest1_lower函数456.27TTest1_upper函数456.28ZTest_sterr函数506.29ZTest_conf函数516.30ZTestw_z函数516.31ZTestw_sig函数526.32ZTestw_dif函数526.33ZTestw_sterr函数526.34ZTestw_conf函数537字段输入统计函数537.1Inputsum函数537.2inputavg函数548高级统计函数568.1Aggr函数569集合分析569.1集合标志579.2集合运算589.3集合修改589.4带集合运算的集合修改609.5Set Modifiers Using Assignments with Implicit Set Operators619.6Set Modifiers with Dollar-sign expansions619.7带有高级搜索功能的集合运算619.8带有隐含字段值定义的集合运算629.9集合的符号集6210虚拟纬度函数6310.1ValueList函数6310.2ValueLoop函数6311基础算数函数6411.1Div函数6411.2mod函数6411.3fmod函数6411.4ceil函数6511.5floor函数6511.6frac函数6511.7round函数6511.8fabs函数6611.9sign函数6611.10numsum函数6611.11Numcount函数6611.12Numavg函数6711.13Nummin函数6711.14Nummax函数6711.15Fact函数6711.16combin函数6711.17permut函数6811.18even函数6811.19odd函数6811.20bitcount函数6812队列函数6912.1Rangesum函数6912.2Rangeavg函数6912.3Rangecount函数6912.4Rangemin函数7012.5Rangemax函数7012.6Rangestdev函数7112.7Rangeskew函数7112.8Rangekurtosis函数7112.9Rangefractile函数7212.10Rangenumericcount函数7212.11Rangetextcount函数7312.12Rangenullcount函数7312.13Rangemissingcount函数7312.14Rangemode函数7412.15Rangeonly函数7412.16Rangeminstring函数7512.17Rangemaxstring函数7512.18Rangecorrel函数7512.19rangeirr函数7612.20rangenpv函数7612.21rangexirr函数7712.22rangexnpv函数7713指数与对数函数7813.1Exp函数7813.2Log函数7813.3log10函数7813.4Sqrt函数7813.5Sqr函数7813.6Pow函数7814三角函数7914.1Cos函数7914.2Acos函数7914.3Sin函数7914.4Asin函数7914.5Tan函数7914.6Atan函数7914.7atan2函数7914.8Cosh函数8014.9Sinh函数8014.10Tanh函数8015静态分布式函数8015.1Chidist函数8015.2chiinv函数8015.3normdist函数8115.4norminv函数8115.5tdist函数8115.6tinv函数8215.7fdist函数8215.8finv函数8316财务函数8316.1BlackAndSchole函数8316.2fv函数8316.3nper函数8416.4pmt函数8516.5pv函数8516.6rate函数8617算术常量函数8617.1E函数8617.2pi函数8617.3rand函数8617.4True函数8717.5False函数8718字符串函数8718.1Ord函数8718.2chr函数8718.3len函数8718.4Left函数8718.5right函数8818.6Mid函数8818.7index函数8818.8upper函数8918.9lower函数8918.10capitalize函数8918.11repeat函数8918.12ltrim函数8918.13rtrim函数9018.14trim函数9018.15KeepChar函数9018.16PurgeChar函数9018.17Replace函数9018.18subfield函数9118.19info函数9118.20TextBetween函数9118.21Findoneof函数9118.22substringcount函数9218.23applycodepage函数9219Inter-Record函数9219.1Fieldvalue函数9219.2fieldvaluecount函数9319.3Column函数9319.4Fieldindex函数9319.5above函数9319.6Below函数9419.7Top函数9519.8Bottom函数9619.9Before函数9719.10After函数9819.11First函数9919.12Last函数10019.13RowNo函数10119.14ColumnNo函数10119.15NoOfRows函数10119.16NoOfColumns函数10219.17Dimensionality函数10319.18Secondarydimensionality函数10320文档函数10320.1ReportComment函数10320.2ReportName函数10420.3ReportID函数10420.4ReportNumber函数10420.5NoOfReports函数10421排名函数10421.1Rank函数10421.2Hrank函数10521.3vrank函数10622条件函数10622.1If函数10622.2alt函数10622.3Pick函数10722.4match函数10722.5mixmatch函数10722.6wildmatch函数10822.7Class函数10823逻辑函数10823.1IsNum函数10823.2IsText函数10924空函数10924.1Null函数10924.2IsNull函数10925系统函数10925.1OSuser函数10925.2QVuser函数10925.3Qlikviewversion函数10925.4ComputerName函数11025.5ReloadTime函数11025.6GetActiveSheetID函数11025.7GetRegistryString函数11025.8Sqlvalue函数11025.9GetCurrentField函数11125.10GetCurrentSelections函数11125.11GetFieldSelections函数11125.12GetSelectedCount函数11225.13GetPossibleCount函数11225.14GetExcludedCount函数11225.15GetAlternativeCount函数11225.16GetNotSelectedCount函数11226日期与时间函数11326.1Day函数11326.2Week函数11326.3Month函数11326.4Year函数11426.5Weekyear函数11426.6Weekday函数11426.7MakeDate函数11426.8MakeWeekDate函数11526.9MakeTime函数11526.10hour函数11526.11minute函数11626.12second函数11626.13now函数11626.14Today函数11626.15LocalTime函数11726.16ConvertToLocalTime函数11726.17Yeartodate函数11826.18DayNumberOfYear函数11926.19DayNumberOfQuarter函数11926.20AddMonths函数11926.21YearStart函数12026.22YearEnd函数12026.23YearName函数12126.24QuarterStart函数12126.25QuarterEnd函数12126.26QuarterName函数12226.27MonthStart函数12226.28MonthEnd函数12326.29MonthName函数12326.30MonthsStart函数12326.31MonthsEnd函数12426.32MonthsName函数12426.33WeekStart函数12526.34WeekEnd函数12526.35WeekName函数12626.36LunarweekStart函数12626.37LunarweekEnd函数12726.38LunarWeekName函数12726.39DayStart函数12826.40DayEnd函数12826.41DayName函数12926.42Timezone函数12926.43GMT函数12926.44UTC函数12926.45daylightsaving函数13026.46setdateyear函数13026.47setdateyearmonth函数13026.48InYear函数13026.49InYearToDate函数13126.50InQuarter函数13126.51InQuarterToDate函数13226.52InMonth函数13226.53InMonthToDate函数13226.54InMonths函数13326.55InMonthsToDate函数13326.56InWeek函数13326.57InWeekToDate函数13426.58InLunarWeek函数13426.59InLunarWeekToDate函数13526.60InDay函数13526.61InDayToTime函数13626.62age函数13626.63networkdays函数13626.64firstworkdate函数13726.65lastworkdate函数13727数值转换函数13727.1num#函数13727.2money#函数13827.3date#函数13827.4time#函数13927.5timestamp#函数13927.6interval#函数14027.7Text函数14028数据格式函数14128.1Num函数14128.2Money函数14228.3date函数14228.4time函数14328.5timestamp函数14328.6interval函数14429统计图颜色函数14529.1Color函数14529.2RGB函数14529.3ARGB函数14529.4colormix1函数14629.5colormix2函数14629.6HSL函数14729.7Black函数14729.8Darkgray函数14729.9Lightgray函数14729.10White函数14729.11Blue函数14829.12Lightblue函数14829.13Green函数14829.14Lightgreen函数14829.15Cyan函数14829.16Lightcyan函数14829.17Red函数14929.18Lightred 函数14929.19Magenta 函数14929.20Lightmagenta函数14929.21Brown函数14929.22Yellow函数15029.23Qliktechblue函数15029.24Qliktechgray函数15029.25Syscolor函数1501 基本统计函数1.1 SUM函数sum ( set_expression distinct total expression )在所有符合集合表达式的记录范围内，按表达式分类求和。Examples:sum( Sales ) 针对某字段求和sum( Price * Quantity ) 表达式求和sum( distinct Price ) 不同的价格的求和，过滤掉相同价格。sum( Sales ) / sum( total Sales ) 指定范围内统计销售额后，再除以全部销售总额，结果是一个比率。sum( Sales ) / sum( total Sales ) 指定范围内统计销售额后，再除以按月统计的销售总额，结果是一个比率。sum( Sales ) / sum( total Sales )指定范围内统计销售额后，再除以按月、组统计的销售总额，结果是一个比率。sum( Sales ) / sum( total Sales )指定范围内统计销售额后，再除以按月、周下钻组统计的销售总额，结果是一个比率。sum( Sales ) / sum( 1 Total Sales )指定范围内统计销售额后，再除以全部记录的销售总额，结果是一个比率。sum(BM01 Sales)返回bookmark：BM01定义范围的销售总额。sum($Sales)返回2007、2008年的销售总额与如下公式效果一样 Sum(if(Year=2007 or Year=2008,Sales)1.2 MIN函数min (set_expression distinct total expression , rank)返回表达式第几小的值，当rank默认为1时，结果是表达式的最小值。Examples:min( Sales ) 指定范围内统计最小的销售额。min( Sales, 2) 指定范围内统计第二小的销售额。min( Price*Quantity )min( total Sales ) 全部选定范围内统计第二小的销售额。min( 1 total Sales ) 全部范围内（用户选择将对统计范围不产生影响）统计第二小的销售额。min( total Sales )1.3 MAX函数max ( set_expression distinct total expression , rank)返回表达式第几大的值，当rank默认为1时，结果是表达式的最大值。Examples:max( Sales )max( Sales, 2)max( Price*Quantity )max( total Sales )max( 1 total Sales )max( total Sales )1.4 Only函数only ( set_expression distinct total expression )返回仅有的一个值，常应用于分组汇总时，如果仅有一个值，则返回，否则返回NULL。Examples:only( Sales )only( Price*Quantity )only( total Salesman )only( 1 total Sales )only( total Salesman )1.5 Mode函数mode( set_expression distinct expression )返回出现频率最多的那个值。如果有两种以上的可能值，则返回NULL。Examples:mode( Product )mode( X*Y/3 )1.6 firstsortedvalue函数firstsortedvalue ( set_expression distinct total expression,sortweight,rank )返回各分组中按照sortweight表达式排序的第n（Rank指定）小的值。（排序由小到大）Examples:firstsortedvalue ( PurchasedArticle, OrderDate )firstsortedvalue ( PurchasedArticle, OrderDate, -2 )返回按照OrderDate字段值排序的倒数第二个值。firstsortedvalue ( A/B, X*Y/3 )firstsortedvalue ( distinct PurchasedArticle, OrderDate )firstsortedvalue ( total PurchasedArticle, OrderDate )firstsortedvalue ( total PurchasedArticle, OrderDate )2 字符串统计函数2.1 MinString函数MinString( set_expression total expression,rank )请参考MIN函数Examples:MinString( Currency )MinString( Left( abc,2 ) )MinString( total Currency )MinString( total Currency )2.2 MaxString函数MaxString( set_expression total expression )请参考MAX函数Examples:MaxString( Currency )MaxString( Left( abc,2 ) )MaxString( total Currency )MaxString( total Currency )2.3 Concat函数concat ( set_expression distinct total expression, delimiter,sort-weight )使用delimiter分隔符将表达式分隔成一组数据，并且按照sort-weight表达式有小到大排序。Examples:concat( Code, ; )concat( FirstName& &LastName, , )concat( distinct Code, ; )concat( total Name, ; , Date )concat( total Name, ; , Date)3 计数统计函数3.1 Count函数count( set_expression distinct total expression )统计表达式的次数。按照total 逐级分组统计。Examples:count( Sales )count( Price*Quantity )count( distinct Price )count( Sales ) / count( total Sales )returns share within selectioncount( Sales ) / count( total Sales )returns share within selection for each Monthcount( Sales ) / count( total Sales )returns share within selection for each Month and Grpcount( Sales ) / count( total Sales )possible syntax for use with a time drill-down groupcount( Sales ) / count( 1 total Sales )returns share within total documentcount( Sales ) / count( distinct 1 total Sales )3.2 NumericCount函数NumericCount ( set_expression distinct total expression| field )参考Count函数Examples:NumericCount( Sales )NumericCount( Price*Quantity )NumericCount( distinct Price )NumericCount( Sales ) / NumericCount( total Sales )returns share within selectionNumericCount( Sales ) / NumericCount( total Sales )returns share within selection for each MonthNumericCount( Sales ) / NumericCount( total Salesreturns share within selection for each Month and GrpNumericCount( Sales ) / NumericCount( total Sales )possible syntax for use with a time drill-down groupNumericCount( Sales ) / NumericCount( 1 total Sales )returns share within total document3.3 TextCount函数TextCount ( set_expression distinct total expression |field )参考Count函数Examples:TextCount( Sales )TextCount( Price*Quantity )TextCount( distinct Price )TextCount( Sales ) / TextCount( total Sales )returns share within selectionTextCount( Sales ) / TextCount( total Sales )returns share within selection for each MonthTextCount( Sales ) / TextCount( total Sales )returns share within selection for each Month and GrpTextCount( Sales ) / TextCount( total Sales )possible syntax for use with a time drill-down groupTextCount( Sales ) / TextCount( 1 total Sales )returns share within total document3.4 NullCount函数NullCount ( set_expression distinct total expression |field )统计表达式中空值的数量；Examples:NullCount( Sales ) 销售量为空的数量NullCount( Price*Quantity ) NullCount( distinct Price ) 销售价格为空的数量NullCount( Sales ) / NullCount( total Sales ) returns share within selection NullCount( Sales ) / NullCount( total Sales )returns share within selection for each MonthNullCount( Sales ) / NullCount( total Sales )returns share within selection for each Month and GrpNullCount( Sales ) / NullCount ( total Sales )possible syntax for use with a time drill-down groupNullCount( Sales ) / NullCount( 1 total Sales )returns share within total document3.5 MissingCount函数MissingCount ( set_expression distinct total expression| field )Examples:MissingCount( SalesMissingCount( if( Price10, Price, invalid ) )MissingCount( distinct Price )MissingCount( Sales ) / MissingCount( total Sales )returns share within selectionMissingCount( Sales ) / MissingCount( total Sales )returns share within selection for each MonthMissingCount( Sales ) / MissingCount( total Sales )returns share within selection for each Month and GrpMissingCount( Sales ) / MissingCount( total Sales )possible syntax for use with a time drill-down groupMissingCount( Sales ) / MissingCount( 1 total Sales )returns share within total document4 统计函数4.1 Avg函数avg ( set_expression distinct total expression | field )返回表达式的平均值Examples:avg( Sales ) 返回平均销售额avg( X*Y/3 ) 返回表达式X*Y/3的平均值avg( distinct Price ) 返回产品的平均价格avg( total Sales ) 返回全部销售额的平均销售额avg( 1 total Sales ) 返回全部销售额的平均销售额，并且不受选择过滤条件限制。avg( total Price ) 按组计算平均价格4.2 stdev函数stdev( set_expression distinct total expression | field )返回表达式的标准差，标准差指各数据偏离平均数的距离（离均差）的平均数。Examples:stdev( Sales )stdev( X*Y/3 )stdev( distinct Price )stdev( total Sales )stdev( 1 total Sales )stdev( total Sales )4.3 median函数median ( set_expression distinct total expression )返回表达式的中间值。Examples:median( X )median( X*Y/3 )median( total X )median( total Price )4.4 fractile函数fractile ( set_expression distinct total expression,fractile )返回表达式的分位数所对应的值Examples:fractile( Sales, 0.75 )fractile( X*Y/3, 0.9 )fractile( total Price, 0.25 )fractile( total Price )4.5 skew函数skew( set_expression distinct total expression | field )返回表达式的偏度Examples:skew( Sales )skew( X*Y/3 )skew( distinct Price )skew( total Sales )skew( 1 total Sales )skew( total Sales )4.6 kurtosis函数kurtosis( set_expression distinct total expression | field)返回表达式的峰度。Examples:kurtosis( Sales )kurtosis( X*Y/3 )kurtosis( distinct Price )kurtosis( total Sales )kurtosis( 1 total Sales )kurtosis( total Sales )4.7 correl函数correl ( set_expression distinct total x-expression, yexpression )根据x，y坐标返回聚合的相关系数。Examples:correl( Payments, Dates )correl( A/B, X*Y/3 )correl( total Payments, Dates )correl( total Payments, Dates)4.8 sterr函数sterr ( set_expression distinct total expression )返回表达式的标准误差。Examples:sterr( X )sterr( X*Y/3 )sterr( total X )sterr( total X)4.9 steyx函数steyx ( set_expression distinct total y-expression, xexpression )根据坐标返回x表达式,y表达式的标准误差。Examples:steyx( Y, X )steyx( A/B, X*Y/3 )stex( total Y, X )steyx( total Y, X)4.10 linest_m函数linest_m (set_expression distinct total y-expression, x-expression , y0 , x0 )Examples:linest_m( Y, X )linest_m( A/B, X*Y/3 )linest_m( total Y, X )linest_m( total Y, X)linest_m( Y, X, 0 )linest_m( Y, X, 1, 1 )4.11 linest_b函数linest_b ( set_expression distinct total y-expression, x-expression , y0 , x0 )Examples:linest_b( Y, X )linest_b( A/B, X*Y/3 )linest_b( total Y, X )linest_b( total Y, X)linest_b( Y, X, 0 )linest_b( Y, X, 1, 1 )4.12 linest_r2函数linest_r2 (set_expression distinct total y-expression, x-expression , y0 , x0 )Examples:linest_r2( Y, X )linest_r2( A/B, X*Y/3 )linest_r2( total Y, X )linest_r2( total Y, X)linest_r2( Y, X, 0 )linest_r2( Y, X, 1, 1 )4.13 linest_sem函数linest_sem ( set_expression distinct total y-expression, x-expression , y0 , x0 )Examples:linest_sem( Y, X )linest_sem( A/B, X*Y/3 )linest_sem( total Y, X )linest_sem( total Y, X)linest_sem( Y, X, 0 )linest_sem( Y, X, 1, 1 )4.14 linest_seb函数linest_seb ( set_expression distinct total y-expression, x-expression , y0 , x0 )Examples:linest_seb( Y, X )linest_seb( A/B, X*Y/3 )linest_seb( total Y, X )linest_seb( total Y, X)linest_seb( Y, X, 0 )linest_seb( Y, X, 1, 1 )4.15 linest_sey函数linest_sey ( set_expression distinct total y-expression, x-expression , y0 , x0 )Examples:linest_sey( Y, X )linest_sey( A/B, X*Y/3 )linest_sey( total Y, X )linest_sey( total Y, X)linest_sey( Y, X, 0 )linest_sey( Y, X, 1, 1