Group at department instead of employee level

[#1]

I am trying to create utilization dashboard for the department where utilization percentage is less than 50.
In the dashboard tehre are two object one is bar chart and other is pivot table

In bar chart dimention is department and expession is utilization %

In Pivot table dimension is department employee name and expression is utilization %

vLowUti=50

Below is expession that I am using

if(
((
SUM({$<[Expenditure Category]={"Chargeable Hours"},[Project Type]={"Contract"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours])
)*100)<= vLowUti,
(SUM({$<[Expenditure Category]={"Chargeable Hours"},[Project Type]={"Contract"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours])

)
)

When I am taking utilization at department then I am getting all those department where utilization is less than 50 but as soon as I insert employee
name then it start giving employee utilization where it is less than 50.
I want to show department as well employee but only for departmnet where utilization is less than 50.

[swuehl]

Not sure if I understand your condition:

SUM({$<[Expenditure Category]={"Chargeable Hours"},[Project Type]={"Contract"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]))*100)<= vLowUti

Don't you need to devide your sum by something (maybe total hours), to get an appropriate percentage?

Besides that, I think you can probably achieve what you want by adding a total qualifier with field list to your sum expression, like

sum(total<department> .....)

Regards,

Stefan

[#3]

You need to use the aggrigated summation to fix this issue. If you can attach your sample QVW file then we can fix it for you.

Regards,

Sajeevan

[CELAMBARASAN]

Hi,

     You have to mention the group in set analysis like below

if(
((
SUM(TOTAL <Department> {$<[Expenditure Category]={"Chargeable Hours"},[Project Type]={"Contract"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours])
)*100)<= vLowUti,
(SUM({$<[Expenditure Category]={"Chargeable Hours"},[Project Type]={"Contract"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours])

)
)

Hope it  Helps

Celambarasan

[#5]

Yes Stefan, You are correct just for simplicity I havent given complete expression, Here is teh complete expression

if(

((

SUM({$<[Expenditure Category]={"Chargeable Hours"},[Project Type]={"Contract"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours])

/

(((SUM({$<FY ={$(vCY)} ,[Bu Admin] = {"N"} >}[Standard Hours])-(SUM({$<[Expenditure Category] ={"Leave Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]))

)-(SUM({$<[Expenditure Category] ={"Training Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]))))

)*100)<= vLowUti or

((

SUM({$<[Expenditure Category]={"Chargeable Hours"},[Project Type]={"Contract"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours])

/

(((SUM({$<FY ={$(vCY)} ,[Bu Admin] = {"N"} >}[Standard Hours])-(SUM({$<[Expenditure Category] ={"Leave Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]))

)-(SUM({$<[Expenditure Category] ={"Training Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]))))

)*100)>=vHighUti,(SUM({$<[Expenditure Category]={"Chargeable Hours"},[Project Type]={"Contract"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours])

/

(((SUM({$<FY ={$(vCY)} ,[Bu Admin] = {"N"} >}[Standard Hours])-(SUM({$<[Expenditure Category] ={"Leave Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]))

)-(SUM({$<[Expenditure Category] ={"Training Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]))))

)

)

[#6]

Yes Stefan, You are correct just for simplicity I havent given complete expression, Here is the complete expression

if(

((

SUM({$<[Expenditure Category]={"Chargeable Hours"},[Project Type]={"Contract"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours])

/

(((SUM({$<FY ={$(vCY)} ,[Bu Admin] = {"N"} >}[Standard Hours])-(SUM({$<[Expenditure Category] ={"Leave Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]))

)-(SUM({$<[Expenditure Category] ={"Training Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]))))

)*100)<= vLowUti or

((

SUM({$<[Expenditure Category]={"Chargeable Hours"},[Project Type]={"Contract"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours])

/

(((SUM({$<FY ={$(vCY)} ,[Bu Admin] = {"N"} >}[Standard Hours])-(SUM({$<[Expenditure Category] ={"Leave Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]))

)-(SUM({$<[Expenditure Category] ={"Training Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]))))

)*100)>=vHighUti,(SUM({$<[Expenditure Category]={"Chargeable Hours"},[Project Type]={"Contract"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours])

/

(((SUM({$<FY ={$(vCY)} ,[Bu Admin] = {"N"} >}[Standard Hours])-(SUM({$<[Expenditure Category] ={"Leave Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]))

)-(SUM({$<[Expenditure Category] ={"Training Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]))))

)

)

[#7]

Hi Sajeevan,

I tried using aggr function but i am getting only first employee name when , I am adding employee name field in dimention.

Below is the query

if(
((
Sum(aggr(SUM({$<[Expenditure Category]={"Chargeable Hours"},[Project Type]={"Contract"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]),Descipline_Utilization))
/
(((Sum(aggr(SUM({$<FY ={$(vCY)} ,[Bu Admin] = {"N"} >}[Standard Hours]),Descipline_Utilization))
-(Sum(aggr(SUM({$<[Expenditure Category] ={"Leave Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]),Descipline_Utilization)))
)-(Sum(aggr(SUM({$<[Expenditure Category] ={"Training Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]),Descipline_Utilization)))))
)*100)<= vLowUti ,
(Sum(aggr(SUM({$<[Expenditure Category]={"Chargeable Hours"},[Project Type]={"Contract"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]),Descipline_Utilization))
/
(((Sum(aggr(SUM({$<FY ={$(vCY)} ,[Bu Admin] = {"N"} >}[Standard Hours]),Descipline_Utilization))
-(Sum(aggr(SUM({$<[Expenditure Category] ={"Leave Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]),Descipline_Utilization)))
)-(Sum(aggr(SUM({$<[Expenditure Category] ={"Training Hours"},FY ={$(vCY)},[Bu Admin] = {"N"}>} [Actual Hours]),Descipline_Utilization)))))
)
)

[#8]

Hi Celambarasan & Stefan,

I tried using total but , I think I am not getting expression right.

Thanks

Nishant

[#9]

Hi Celambarasan & Stefan,

I tried using total but , I think I am not getting expression right.

Thanks

Nishant

[#10]

Can you attach a sample QVW file? You need to get the NONDISTINCT aggrigated value wherever it is required.