Average of Daily total

[Suxel]

Hi, 

I'm new in Qlik Sense, facing issue on average.

I have created several formula to calculate Productivity%,

SET TotalTimeSpent =Sum([Time Spent (mins)]);
SET AvailableDAY =Count(distinct total <Date, Team, UserID> Date);
SET AvailableFTE =Sum(Total <Date, Team, UserID> Aggr(Count(distinct total <Date, Team, UserID> UserID),Date, Team, UserID)) / $(AvailableDAY);
SET Productivity% =$(TotalTimeSpent) / ($(AvailableFTE)) / $(AvailableDAY) / 480;

The daily productivity% returns are perfect, but the average productivity% are running the formula again instead of just average the daily productivity%. In below screenshots, if calculate with excel, the average productivity% should be 45% instead of 48%. How can I get the 45% instead of 48% in Qlik Sense?

Qlik Sense Calculation

Excel Calculation

[rubenmarin · Accepted solution]

Hi, on the measure definition you can change the totals type to average instead of auto.

If this doesn't works you can try with aggr:

Avg(Aggr($(Productivity%), Date))

[Suxel · Accepted solution]

Hi, million thanks for the idea to Aggr() $(Productivity%), it works exactly as I wish. At the end, I have used Sum() and divide with a base instead of using Avg() as Avg() ignore blank row. 

SET TotalTimeSpent =Sum([Time Spent (mins)]);
SET AvailableFTE =Count(distinct total<Date, Team, UserID> UserID);
SET AvailableDAY =Count(distinct total<Date, Team, UserID> Date);
SET Productivity% = $(TotalTimeSpent)/$(AvailableFTE)/480;
SET TeamProductivity% = Sum(Aggr($(Productivity%),Date, Team, [Process Category]))/$(AvailableDAY);
SET StaffProductivity% =Sum(Aggr($(Productivity%),Date, UserID, [Process Category]))/$(AvailableDAY);

Besides, changing the measure definition from AUTO will work in the table chart only, with the Aggr() function I can now use in all type of charts.