Obtaining a count of types from an expression

[#1]

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

[#2]

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