Average of average

[emilyrabbit]

Hi buddy, I met a average of average issue.

Attached is the excel example.I want the avg order per sales admin. the chart is what I want in the qlik sense. pls help me , thanks in advance.

In the qlik sense, I put [Year Month] in the dimension. and put below expression in the measure. 

[Avg Order Per Sales Admin]=average(
(count(distinct[Order Document Number]))/
(count(distinct[Order Created By Rep]))

)

the result failed.

[hanna_choi · Accepted solution]

Hello @emilyrabbit 

How about you are applied the calculated values using variables to visualization?

vAvg Expression : 

=(Count( Order)/Count( distinct [Sales Admin])) / Count(distinct [Year Month]) 

[emilyrabbit]

Awesome! Thanks.

[emilyrabbit]

Hi ,thanks for your detailed explanation. Could I ask a stupid question? Why using variable visualization , Count(distinct [Year Month] , can avoid the year month filter when I put it in the X-axis ? 

[hanna_choi]

Hi @emilyrabbit 

Does it mean that if you select a [Year_Month] on the X-axis, it will not be filtered?

In my app, when I select a [Year_Month], it is only filtered by the [Year_Month] I select.

I am sharing my app with you.

[emilyrabbit]

Hi， sorry, I didn't represent myself well. My question is : if I didn't create a variable . just create a measure:

Expression A : 

=(Count( Order)/Count( distinct [Sales Admin])) / Count(distinct [Year Month]) 

and put the Expression A into a chart , and the X-axis of the chart is [Year Month] , I found the Expression A is not the same in each [Year Month].

However , if I create a variable 

Expression B : 

=(Count( Order)/Count( distinct [Sales Admin])) / Count(distinct [Year Month]) 

and put the Expression B into a chart , and the X-axis of the chart is [Year Month] , I found the Expression B is the same in each [Year Month].

I am curious why variable can ignore the [Year Month] in X-axis.

Thanks for any information.

[hanna_choi]

Hi @emilyrabbit 

I understand the Variables and the Expression as follows :

- The variables with equal signs(=) are calculated values, , so it is displayed as one value.

   If the variable does not use '=', it is dynamically calculated in the formula of the graph.

- The expression of the graph is a dynamically calculated value based on the dimensions.