Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Blunck-TotalFitouts
Contributor III
Contributor III

Avg of an avg

Hi everyone,

I need to work out the average of an average... + not have the result change when selecting a dimension.

The standard average formula of:

Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.Jobs])
/
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.CalMonthYear])

Works when calculating the average number of jobs per cal month for ALL current and past franchises in total. 

However, what I need to know is the average franchise partner jobs per month value.  Figured I could use this:

=Avg({<[Franchise Partners All]=>}Aggr(
Only({<[Franchise Partners All]=>}Count(distinct [Jobs.Jobs])
/
Count(distinct [Jobs.CalMonthYear])), [Franchise Partners All]))

Note - [Franchise Partners All] just has values for the current and past franchise partners.

However this formula produces a "-". Result.

Updating to this:

Avg(Aggr(
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.Jobs])
/
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.CalMonthYear])
,[Franchise Partners All]))

Produces a result.  But it doesn't work for because when a franchise partner is chosen through a filter the result get's updated to match the result for that franchise partner, which is what I don't want.

Please help!

Thanks

Carl

Labels (1)
  • SaaS

1 Solution

Accepted Solutions
Kashyap_R
Partner - Specialist
Partner - Specialist

Hi

try this

Avg(All Aggr(
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.Jobs])
/
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.CalMonthYear])
,[Franchise Partners All]))

OR

Avg(Aggr(All
(Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.Jobs])
/
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.CalMonthYear]))
,[Franchise Partners All]))

Hope this helps

Thanks

Thanks and Regards
Kashyap.R

View solution in original post

2 Replies
Kashyap_R
Partner - Specialist
Partner - Specialist

Hi

try this

Avg(All Aggr(
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.Jobs])
/
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.CalMonthYear])
,[Franchise Partners All]))

OR

Avg(Aggr(All
(Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.Jobs])
/
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.CalMonthYear]))
,[Franchise Partners All]))

Hope this helps

Thanks

Thanks and Regards
Kashyap.R
Blunck-TotalFitouts
Contributor III
Contributor III
Author

Legend, the 1st one worked!

Thanks so much!