Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
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
Partner
Partner

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

View solution in original post

2 Replies
kashyap
Partner
Partner

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

View solution in original post

Blunck-TotalFitouts
Contributor III
Contributor III
Author

Legend, the 1st one worked!

Thanks so much!