Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a set of employees and data on the projects they are working on.
In a pivot table I use the following formula to work out the percentage they are billable:
=Sum({$<[Time category group] = {"Productive"}>+ <[Time category group] = {"Billable"}>} [Project time])/sum([Project time])
I then use the visual clues facility to highlight cases above 90% as Green, 80-90% - amber and the rest are Red.
In addition to the pivot table I want to create a count of employees that are Red, Amber and Green.
I can create the ‘colour’ as a first expression in a new object:
= if(Sum({$<[Time category group] = {"Productive"}>+ <[Time category group] = {"Billable"}>} [Project time])/sum([Project time]) < 0.8, 'Red',if(Sum({$<[Time category group] = {"Productive"}>+ <[Time category group] = {"Billable"}>} [Project time])/sum([Project time])>= 0.9,'Green','Amber' ))
I then tried counting the results of the first column:
=if( Column(1) = 'Amber', count(DISTINCT [Name of employee]) )
This returns a value of one. I have tried numerous other permutations with count and agg and sum, but I do not seem to be able to obtain a count.
Does anyone have any ideas?
Regards
Jason
Hi,
In the below expression I am trying to find the count of distinct products for which % Change in Cost is >=0.
=
COUNT({<Product={"=if((Sum( {1<Product=p(Product)>} Cost) -
Sum( {1<Product=p(Product)>} Cost_Change))=0,0,
Sum( {1<Product=p(Product)>} Cost_Change)/
(Sum( {1<Product=p(Product)>} Cost) -
Sum( {1<Product=p(Product)>} Cost_Change)))>=0"} >} DISTINCT Product)
But this expression is not returning a correct value if denominator is zero ie if
Sum( {1<Product=p(Product)>} Cost) -
Sum( {1<Product=p(Product)>} Cost_Change)=0
Thanks,
Amit