# Qlik Sense App Development

New Contributor

## Text field average

 Transportation Mode Identifier Day Regularity Train 2 1/6/2018 1 Train 3 1/6/2018 1 Train 7 2/17/2018 1 Train 11 2/17/2018 1 Train 22 2/17/2018 1 Train 22 3/17/2018 1 Plane 1 3/17/2018 1 Plane 1 3/17/2018 1 Plane 4 3/17/2018 1 Plane 9 3/17/2018 1 Plane 9 3/17/2018 1 Plane 19 3/17/2018 1 Plane 21 3/17/2018 1 Car 5 1/6/2018 1 Car 5 1/6/2018 1 Car 5 1/6/2018 1 Car 10 1/6/2018 1 Car 13 1/6/2018 1 Car 18 1/6/2018 1 Car 20 2/17/2018 1 Car 20 3/17/2018 1 Boat 6 1/6/2018 1 Boat 8 2/17/2018 1 Boat 12 2/17/2018 1 Boat 14 2/17/2018 1 Boat 15 2/17/2018 1 Boat 16 3/17/2018 1 Boat 17 3/17/2018 1

In my sheet I have a table with Transportation Mode as the dimension.  I want to include a measure that calculates the average number of times each Transportation Mode (Train, Plane, Car, Boat) occurs if each classifier is only counted once.

Here is my formula but it is not working, can you assist?

Avg(Aggr((Transportation Mode),Distinct[Classifier])

This is the result I am trying to get

 Transportation Mode 1/6/2018 2/17/2018 3/17/2018 AVG Train 2 3 1 2.0 Boat 1 4 2 2.3 Car 6 1 1 2.7 Plane 0 0 7 2.3
1 Solution

Accepted Solutions
Valued Contributor II

## Re: Text field average

So

make a pvot table with dimensions Transportation and Day

in expression :

if(SecondaryDimensionality()=1,  count( Identifier),sum(aggr(count(Identifier), [Transportation Mode],Day ))/ count(total distinct  Day))

with subtotal for Day

secondarydim is used because subtotal is not the same as expression

regards

3 Replies
Valued Contributor II

## Re: Text field average

Hi,

just use this :

Train : 2,3,7,11,22

= 45/5 = 9

is it what you want ?

regards

New Contributor

## Re: Text field average

Hi Olivier,  your question helped me see how I could improve my question, so I made the change and posted above.  I am trying to turn this...

 Transportation Mode Identifier Day Regularity Train 2 1/6/2018 1 Train 3 1/6/2018 1 Train 7 2/17/2018 1 Train 11 2/17/2018 1 Train 22 2/17/2018 1 Train 22 3/17/2018 1 Plane 1 3/17/2018 1 Plane 1 3/17/2018 1 Plane 4 3/17/2018 1 Plane 9 3/17/2018 1 Plane 9 3/17/2018 1 Plane 19 3/17/2018 1 Plane 21 3/17/2018 1 Car 5 1/6/2018 1 Car 5 1/6/2018 1 Car 5 1/6/2018 1 Car 10 1/6/2018 1 Car 13 1/6/2018 1 Car 18 1/6/2018 1 Car 20 2/17/2018 1 Car 20 3/17/2018 1 Boat 6 1/6/2018 1 Boat 8 2/17/2018 1 Boat 12 2/17/2018 1 Boat 14 2/17/2018 1 Boat 15 2/17/2018 1 Boat 16 3/17/2018 1 Boat 17 3/17/2018 1

into this...

 Transportation Mode 1/6/2018 2/17/2018 3/17/2018 AVG Train 2 3 1 2.0 Boat 1 4 2 2.3 Car 6 1 1 2.7 Plane 0 0 7 2.3
Valued Contributor II

## Re: Text field average

So

make a pvot table with dimensions Transportation and Day

in expression :

if(SecondaryDimensionality()=1,  count( Identifier),sum(aggr(count(Identifier), [Transportation Mode],Day ))/ count(total distinct  Day))

with subtotal for Day

secondarydim is used because subtotal is not the same as expression