Facing Issue witSet Analysis Exp to get Avg of Variance Time

[#1]

Hi friends I am stuck with an Issue on Set Analysis please do needful on the Issue

I have data about how effective Resources are utilized in a day/month,

In a day each Resource works for 8 Hours,

I have a dimensions with

     PRJ_NM(Project Name)
STAFF_PROF_NM()
RSRC_NM,
month(ALLOC_DATE)as Month_Alloc,
ALLOC_VAL

To calculate avg used below expresson for each/month

sum(aggr(avg((ALLOC_VAL)),PRJ_NM,RSRC_NM,Month_Alloc))/8

As some resources are not utilized 100% need to help on how to calculate the Resource free Time from total

[MK_QSL]

For variance, you can use below expression

1-Column(1)

[#3]

can you please look into qvw and provide solution as i am not getting your solution

[MK_QSL]

File enclosed..

[#5]

Sorry from my end as I Assumed Allocation_Value as 1 will be Max(i.e.,1=100%)but for some of the Resources Allocation Value is 1.4,1.2,1.6,2.4,.....

Please look into the Attachment for any further reference.

can you please guide me how to write below

sum(aggr(avg(ALLOC_VAL),PRJ_NM,RSRC_NM,Month_Alloc,MANAGER_NAME))/8 as i Assumed to Allocation Value is 1,but for some of Resources>1

thanks

[#6]

hi manish,

                when i select on month,avg Allocation is 1 but if i select avg allocation based on years the Allocation value is different, mean sum of all months = year allocation value.

Requirement is when i select on year it should show me the avg allocation value based on years and what was the Variance AND

when i select on the Month it should show me the avg allocation based on months value and what was the Variance.

Regards,

Bhargav