Display records where the value is <50%

[#1]

I have got a requirement to display only those records whose value is less than 50%.

I am using the below expression but i am not getting the desired output.

= If(Aggr(Sum({$<$(vUtlExceptions)>}Utilization)/Sum({$<$(vUtlExceptions)>}Available),<.5),Name)

I would appreciate if someone can help me to figure out the correct expression to display the name of those employees whose Utilization is less than 50%.

Regards

Vineet

[#2]

Vineet,

it will be the best if you share sample.

regards

Darek

[its_anandrjs]

Can you elaborate more or share the sample files will be better for the approach.

[#4]

Please find the attached sample data. There in that attached 'Utilizationworkbook.xls',there are two worksheet one contains the sample data and the other contains the desired output.

I hope this is what you were looking for.

Do let me know if you need any further information.

Regards

Vineet

[hic]

This is typically something you should not hard-code. Instead, create a field "Utilization" in the script:

          UtilizationHrs /  AvailableHrs as Utilization,

Then make a numeric search in the list box, and hit Enter to select the values.

Voilà! You have the answer.

HIC

[#6]

Thanks Henric for looking into my problem,but i am sorry to say that you didn't got my requirement.

I have got those three Fields Name,UtilizationHrs and AvailableHrs.

I need to show the name of the resources whose %Utilization is less than 50% in a straight table.

The below Expression gives me the %Utilization.

Sum({$<$(vUtlExceptions)>}Utilization)/Sum({$<$(vUtlExceptions)>}Available)

I am looking for an Expression/Formula which will return the records whose %Utilization is less than 50%.

I need to use an expression i cannot tweak the load statement to meet the above requirment.

[hic]

Oh, I understood your requirement perfectly well. I just think it is very wrong to hard-code something like that.

The above suggested solution will solve your problem. Make a selection like that, and you will get the correct names listed in the "Name" list box.

An alternative solution is to make the same search in the straight table. Make your expression searchable (Presentation tab), click the magnifying glass and enter '<0.5' :

This will also reduce the data to the names you are interested in.

A third way is to hard-code the same thing - god forbid. Then you should use the following as expression:

     If(Sum(UtilizationHrs)/Sum(AvailableHrs)<0.5, Sum(UtilizationHrs)/Sum(AvailableHrs))

and only the relevant names will be shown. If the utilization rate is more than 50%, the expression will evaluate to NULL, and these names will not be visible.

HIC

[#8]

Well I liked that 2nd idea of yours the one where you suggested to make the expression searchable.

But anyway I was able to resolve the problem by adding a Calculated dimension.

Thanks for your invaluable suggestions .

[#9]

Hi Henric

I discussed your idea with the team and everybody liked it ...but the problem is we looked into the Presentation tab of %Utilization Dimension but unfortunately didn't find any component which will make the expression searchable.

Would appreciate if you can guide me on how to do this.

Thanks

[hic]

The Utilization should be an expression, not a dimension:

     Sum (UtilizationHrs)/Sum (AvailableHrs)

and then the Presentation tab will look like the following:

HIC