01高級分析模型設(shè)計培訓(xùn)函數(shù)應(yīng)用qonnections workshop advanced functions in qlikview

1、Workshop: Advanced functions in QlikViewMarcus Kirsebom - Lund, SwedenWiko Law Stockholm, SwedenAgenda 13.30 13.40Introduction13.40 14.00Walkthrough of set analysis.Pros and cons? What were R&Ds ideas when first introducing this function?Whats to come? Examples, different ways to use set analysis14.

2、00 14.45Exercises14.45 15.15Presentations and discussions15.15 15.45Coffee break15.45 16.15Aggr, calculated dimensions and range functions16.15 17.00Exercises17.00 17.30Presentations and discussionsAgenda13.20 13.30Introduction13.30 13.50Walkthrough of set analysis.Pros and cons? What were R&Ds idea

3、s when first introducing this function?Whats to come? Examples, different ways to use set analysis13.50 14.35Exercises14.35 15.05Presentations and discussions15.05 15.35Coffee break15.35 16.05Aggr, calculated dimensions and range functions16.05 16.50Exercises16.50 17.20Presentations and discussionsI

4、ntroductionGoal with this session: To get a better understanding what business problems the covered functions has solved and how they can be used. To gain a better understanding how to use these functions and their syntax.Starting discussions regarding uses of these functionsSpreading knowledgeFeel

5、free to interrupt!What is Set Analysis?By using Set Analysis you can specify a set selection that overwrite the current selection set in an aggregated function. It is possible to compare several different sets in the same chart. The current selection set is Nice, Paris and SeattleThe set selection i

6、s ”hardcoded” to LundThe set selection may be based on:a bookmarkas a modification of the current selectionthe inverse of current selectionprevious selectionall dataetcSet Analysis Syntaxsum( set identifier Sales)Set identifiers:$current selection set1full selection setMyBookMarkselection set define

7、d by the bookmark MyBookMark$1previous selection setExamples:sum( $ Sales)sales for current selectionsum( 1 Sales)sales for total selectionsum( 1-$ Sales)sales for the invers of current selectionSet Analysis Syntaxsum( $ Sales)Set modifier is used for making modifying selection set Set modifier synt

8、ax is Examples:sum( $ Sales)Sales for year 2008sum( $ Sales)Sales for all regions.sum( $ Sales)Disregard selections in field Region.Set Analysis SyntaxAdvanced modifierUsing search function in set modifiersum( $ Sales) sum( $ Year = ” Sales) Using variable in set modifiersum( $ Sales)Using evaluatio

9、n in set modifiersum( $ Sales)sum( $ Sales)sum( $ Year = Year, Month = “ Sales)Using nested set expressionsum( $Customer = “=Sum(1 Sales ) 1000000” Sales)Set analysis Thoughts from R&DUsing set analysis is more efficient than flags, if statements.A set expression with a string comparison instead of

10、numeric value is more or less as efficient, no big difference.Be careful when using set analysis. Youre moving away from standard QlikView functionality where everything is connected.Use help texts to describe what answers the expressions give.Set analysis Pros and consPros:Empowering the Profession

11、al users.Dynamic solutions, apply changes without reloading data.Easier to compare, e.g. year-to-year comparisonCons:Easily complex expressions. Difficult to follow.Moving away from standard QlikView functionality.Higher demands on the QlikView Developer.Set analysisExamples on different uses of set

12、 analysis:Instead of date- & report flags e.g. rolling 12.Showing current selections in relation to total data set graphically. Excellent for overview pages.Free comparison, with bookmarksDiscussion Set Analysis ExercisePros and cons of different solutions.Set analysisNews in QlikView 9.0 :Indirect

13、set analysis.Syntax:( $Customer = P(1 Customer) Sales )( $Customer = E(1 Customer) Sales )Simplifying previously advanced expressions.Enabling expressions previously not possible. sum( $Customer = P(1 Supplier) Sales )Colour coding of the set analysis syntax.Set analysisNews in QlikView 9.0, continu

14、ed:Example:8.5:sum($CustomerID = = sum(1Sales) 0 and sum(1Sales) 0Sales)9.0:sum($CustomerID = P(1) * P(1)Sales)Aggr, Calculated Dimensions and Range FunctionsRequests from participantsAggr FunctionCreates a vector containing aggregated values grouped by dimensionSyntax: Aggr(expression, dimension/s)

15、Ex: Aggr(sum(Sales), SalesPerson)Ex: Avg(Aggr(sum(Sales), SalesPerson)Always preferable to create aggregations in the script, use aggr when it has to be dynamicIs also used to get sum of row totals in pivot tablesCalculated dimensionsCreates a dimension that doesnt already existOften used together w

16、ith the aggr functionAgain, always preferable to do this in the script, use calulated dimensions when the dimension has to be dynamicTwo main uses:Limit which values that are shown in the dimension=If(Country = Sweden, Customer)Create a dimension where the values are aggregated=Aggr(Count(Customer, SalesRep)Range functionsBy request from Henric Cronstrm, functions considered to be underused Used to make calculations on a range of values, both vertic