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: 
bhuprakash
Creator II
Creator II

Want to show Average Farmer Reporting per day based on average of all Supervisors Reporting per Day

Hi Experts,

I have one query. As per below chart "Average farmers reporting per day" for Total is 36.8 which is correct..

Supervisor wise average is also correct which is calculated as per below formula -

Total Farmer Reach Count/ count of unique dates 

But is there any possibility that I can show average of all supervisors average which is 7.1 

So how can be is possible ? because I want to highlight all supervisors who are meeting farmers below of overall average.

Untitled.png

Labels (1)
2 Solutions

Accepted Solutions
Kushal_Chawda

try to use dimensionality function

if(dimensionality()=1,

avg(aggr(sum[Farmer Reach Count]/ count(distinct floor(mydate)), [Supervisor Name])),

sum[Farmer Reach Count]/ count(distinct floor(mydate)))

View solution in original post

Kushal_Chawda

may be in background color you can put below expression

=if( avg(aggr(sum([Farmer Reach Count])/count(distinct floor(mydate)),[Supervisor Name])) <

sum([Farmer Reach Count])/count(distinct floor(mydate)), lightred())

or

=if( avg(aggr(sum([Farmer Reach Count])/count(distinct floor(mydate)),[Supervisor Name])) <

avg(total aggr(sum([Farmer Reach Count])/count(distinct floor(mydate)),[Supervisor Name])),lightred())

View solution in original post

7 Replies
lironbaram
Partner - Master III
Partner - Master III

hi 

it should be something like 

avg(aggr(Total Farmer Reach Count/ count of unique dates ,Supervisor Name))

bhuprakash
Creator II
Creator II
Author

Hi,
I have applied formula as below -
=avg(aggr(sum[Farmer Reach Count]/ count(distinct floor(mydate)), [Supervisor Name]))

but below output is coming -

Untitled.png

Kushal_Chawda

try to use dimensionality function

if(dimensionality()=1,

avg(aggr(sum[Farmer Reach Count]/ count(distinct floor(mydate)), [Supervisor Name])),

sum[Farmer Reach Count]/ count(distinct floor(mydate)))

bhuprakash
Creator II
Creator II
Author

Thanks a lot Kuch.. Really you are champ !!

Below formula worked correctly -

if(dimensionality()=1, avg(aggr(sum([Farmer Reach Count])/count(distinct floor(mydate)),[Supervisor Name])),
avg(aggr(sum([Farmer Reach Count])/count(distinct floor(mydate)),[Supervisor Name])))

bhuprakash
Creator II
Creator II
Author

Hi Kuch,

Adding to this, now I want to highlight all the supervisor wise average values which are lesser with overall average.

How can I do it?

 

 

Kushal_Chawda

may be in background color you can put below expression

=if( avg(aggr(sum([Farmer Reach Count])/count(distinct floor(mydate)),[Supervisor Name])) <

sum([Farmer Reach Count])/count(distinct floor(mydate)), lightred())

or

=if( avg(aggr(sum([Farmer Reach Count])/count(distinct floor(mydate)),[Supervisor Name])) <

avg(total aggr(sum([Farmer Reach Count])/count(distinct floor(mydate)),[Supervisor Name])),lightred())

bhuprakash
Creator II
Creator II
Author

Second expression worked perfectly. Thanks a lot