How to do Descriptive Statistics in Qlik Sense

How to do Descriptive Statistics in Qlik Sense? 

What I mean by Descriptive Statistics is to display Mean,  Standard Error, Median, Mode, Standard Deviation, Sample Variance, Kurtosis, Skewness, Range, Minimum, Maximum, Sum, Count in one table.

This is really easy to do in Excel. All you need to do is go to 'Data' tab on top ribbon. Then 'Data Analysis'. Then 'Descriptive Statistics'. You select the input and then output and that is it. Please see attached image to know what I mean.

Where do I find this feature in Qlik Sense?

Link to image

Qlik has functions for each of those items:

• Sum()
• Count()
• Avg()
• StErr()
• Median()
• Mode()
• StDev()
• Sqr()
• Kurtosis()
• Skew()
• Max()
• Min()

As far as applying those functions, you can achieve this in three ways.

Option 1: Data Load Editor

You can use script like this:

[Stats]:
Load
    // [Group fields here...]

    Sum([MyField]) 		as [MyField Sum]
  , Count([MyField]) 	as [MyField Count]
  , Avg([MyField]) 		as [MyField Mean]
  , StErr([MyField]) 	as [MyField Standard Error]
  , Median([MyField]) 	as [MyField Median]
  , Mode([MyField]) 	as [MyField Mode]
  , StDev([MyField]) 	as [MyField Standard Deviation]
  , Sqr(StDev([MyField])) as [MyField Sample Variance]
  , Kurtosis([MyField]) as [MyField Kurtosis]
  , Skew([MyField])		as [MyField Snewness]
  , Max([MyField]) - Min([MyField]) as [MyField Range]
  , Min([MyField]) 		as [MyField Min]
  , Max([MyField]) 		as [MyField Max]
Resident [Transactions]
  // Group By [Group fields here...]
;

Option 2: Sheet - hard-coded

You can use a straight table or pivot table object and use the requisite functions as separate fields, like so:

• Sum: =Sum([MyField])
• Count: =Count([MyField])
• Mean: =Avg([MyField])
• Standard Error: =StErr([MyField])
• Median: =Median([MyField])
• Mode: =Mode([MyField])
• Standard Deviation: =StDev([MyField])
• Sample Variance: =Sqr(StDev([MyField]))
• Kurtosis: =Kurtosis([MyField])
• Snewness: =Skew([MyField])
• Range: =Max([MyField]) - Min([MyField])
• Min: =Min([MyField])
• Max: =Max([MyField])

Option 3: Sheet - dynamic

With some extra effort, you can make it so that users can select fields from a filter pane to see descriptive stats in a table dynamically.

Assuming a pivot table is used, you can use this expression as the dimension:

=ValueList( '$(=Concat(distinct [$Field], ''', '''))' )

This uses the ValueList() and Concat() functions to get the list of available fields from the app's data model.

<Edit 1 for more info:>

The key here is the use of the $Field system field, which is a field that is generated automatically by Qlik whenever you reload the data model. They are "hidden" fields, so they don't show up in the Assets pane or the selections bar.

The values of the field include all available fields from the data model. We use this because we want for the user to be able to select which fields are shown in our Descriptive Stats pivot table.

Here's what the expression is doing:

• =Concat(distinct [$Field], ''', ''') <-- Here, we are concatenating each distinct field in our data model, separating each field name with "quote, apostrophe, space, quote" like ', '. The reason we use three quotes together is because we want to include a quote in our resulting string so we have to escape it by using two quotes together.
• '$(= Concat(...) )' <-- The Concat() function from above is "dollar sign expanded," meaning it is evaluated in-place. Note the quotes used ('$(...)' ) -- this means that the result of our dollar sign expansion will be a string.
• When Qlik evaluates the above expression, it will see:
  • =ValueList( 'SalesAmount', 'MyField', 'Quota Number', etc. )
• ...instead of:
  • =ValueList( '$(=Concat(distinct [$Field], ''', '''))' )
  • ^^That's the usefulness of the dollar sign expansion in this case.
• The ValueList() function is used to generate a "synthetic dimension", which is essentially a on-the-fly field that is precluded from being used in selections. It's entirely ephemeral. Now, normally you cannot use this function "dynamically" -- the function expects only a list of hard-coded strings as parameters. We get around this by using the dollar sign expansion shown in the above examples.

</Edit 1>

For our measures, we'll have to use some tricky script to dynamically insert our field values. This is an example of how you could do this for the Sum() function:

=Pick(Match( ValueList( '$(=Concat(distinct [$Field], ''', '''))' ), '$(=Concat(distinct [$Field], ''', '''))')
   , Sum([$(=Concat(distinct [$Field], ']' & chr(41) & ' , Sum' & chr(40) & '[' ))])
)

This uses the Pick() and Match() functions to choose the correct dynamically-created Sum() function for each of our selected fields.

<Edit 2 for more info:>

Here's how this expression works:

• The Pick(Match(...), ...) part of the expression is a way to do pattern matching in Qlik without having to use If() statements (and since switch can't be used in chart expressions). Match() returns the index of the item in a list of parameters which Pick() then uses to choose a corresponding value. In this case we use them to say, for example, "when the field name is 'SalesAmount' pick the =Sum(SalesAmount) expression; when the field name is 'MyField' pick the =Sum(MyField) expression", etc.
• Match( ValueList( '$(=Concat(distinct [$Field], ''', '''))' ), '$(=Concat(distinct [$Field], ''', '''))') <-- This achieves the matching behavior described above.
• Sum([$(=Concat(distinct [$Field], ']' & chr(41) & ' , Sum' & chr(40) & '[' ))]) <-- This expression uses dollar sign expansion (described in the Edit 1 above) to create a list of Sum() functions for each field name. So instead of:
  • Sum([$(=Concat(distinct [$Field], ']' & chr(41) & ' , Sum' & chr(40) & '[' ))])
• ...Qlik will see:
  • Sum([SalesAmount]), Sum([MyField]), Sum([Quota Number]), etc.

</Edit 2>

You can download my example QVF file here.