Qlik Community

Qlik Sense App Development

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

liamlucas
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

  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
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

just add regularity too

regards

3 Replies
ogautier62
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

liamlucas
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

  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
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

just add regularity too

regards

Community Browser