Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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.
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))
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.