Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
liamlucas
Contributor II
Contributor II

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

  1. 2.0

Boat

1

4

2

  1. 2.3

Car

6

1

1

  1. 2.7

Plane

0

0

7

  1. 2.3
1 Solution

Accepted Solutions
ogautier62
Specialist II
Specialist II

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

just add regularity too

regards

View solution in original post

3 Replies
ogautier62
Specialist II
Specialist II

Hi,

just use this :

Train : 2,3,7,11,22

= 45/5 = 9

is it what you want ?

regards

liamlucas
Contributor II
Contributor II
Author

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

  1. 2.0

Boat

1

4

2

  1. 2.3

Car

6

1

1

  1. 2.7

Plane

0

0

7

  1. 2.3
ogautier62
Specialist II
Specialist II

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

just add regularity too

regards