Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
BeuranHendri
Contributor
Contributor

Calculating row wise Average instead of Total in the Pivot Table?

Hello wizards,

I have a requirement in which I need to display employees full time equivalent (FTE) hours per month for a 13 month period. I developed the report using a pivot table and everything seems fine except the Total column (I enabled show Totals). The user wants to see the average of the FTE hours instead of the Total column.

I created a measure to calculate the FTE hours using the below expression:

If(Getselectedcount(TimePeriod)=0,
Sum({<MTD_HRS-={0}, [TimePeriod]={">=$(=Date(Addmonths(Today(),-12),'MMM-YYYY'))<=$(=Date(Today(),'MMM-YYYY'))"}>} MTD_TOT_HRS)/STD_REG_HRS,
Sum({<MTD_HRS-={0}, [TimePeriod]={">=$(=Date(Addmonths(Max(TimePeriod),-12),'MMM-YYYY'))<=$(=Date(Max(TimePeriod),'MMM-YYYY'))"}>} MTD_TOT_HRS)/STD_REG_HRS)

Field description:

TimePeriod = month-year data

MTD_TOT_HRS = total hours worked in month

STD_REG_HRS = total hours an employee is supposed to work in a month (fixed value)

EMP_NAME = Employee Name (in pivot chart)

EMP_NUM = UPI (in pivot chart)

Sub Functional Area = Sub Functional Area (in pivot chart)

 

I also tried to calculate average FTE hours expecting to see the average value in the Total column, but then values in Total column dont match with the expected avg values. I am unable to figure out how to implement this. Kindly assist me with this requirement.

Labels (1)
1 Reply
Digvijay_Singh

Not sure but what if you add Avg(Aggr(current expression,Chart dimension)), as I recall the total property is not a drop down in the pivot chart but just want to check if above modification makes any difference..