Help with variables and greater than

[#1]

Hi,

I'm slowly grasping how variables work, but am having a bit of trouble with the syntax.

I'm trying to count a distinct number of [Billing code]'s where the current year is 2013, the status not equal to Admin and there's some cost booked against it ([Hours Worked * Rate).

The year and status work fine, but I'm not able to format the calculation for hours as I'm trying to calculate a range, i.e where the cost is between £1000 and £4999, £5000 - £9999, etc.

Any help would be greatly appreciated.

=Count(DISTINCT {< [Year]={$(vCurrentYear)}, [Project Status]-={'Admin'}, ([Hours Worked] *if(IsNull(Rate), 20, Rate)) <1000 >} [Billing code])

[jfkinspari]

I would calculate the cost in the script ( ([Hours Worked] *if(IsNull(Rate), 20, Rate)) ) as TotalCost, so you can refer to this field in the Set Analysis. Alternative calculate it in a variable.

Your new function would then look something like

=Count(DISTINCT {< [Year]={$(vCurrentYear)}, [Project Status]-={'Admin'}, TotalCost={">=1000<=5000"} >} [Billing code])

[#3]

Thank you. Yep, that makes sense.

I tried adding it as a variable (as that's something I think I can do)

Created a variable called vTotalCost, with the following formula

[Hours Worked] *if(IsNull(Rate), 20, Rate)

Then added the following in to the text object:

=Count(DISTINCT {< [Year]={$(vCurrentYear)}, [Project Status]-={'Admin'}, $(vTotalCost)={">=1000<=5000"} >} [Billing code])

I also tried

=Count(DISTINCT {< [Year]={$(vCurrentYear)}, [Project Status]-={'Admin'}, $(vTotalCost)={'>=1000<=5000'} >} [Billing code])

replacing the "s with 's, but neither worked, I get '-' as a result.

Any ideas what I might be doing wrong?

[jfkinspari · Accepted solution]

I suspect this might be related to the granularity of the data used in the variable. Is Hours Worked and Rate on the same level?

Try and make a straight table, where you display the result of the variable. If you can't make it display a number, you might need to add a sum() to variable, but it depends on the datastructure

sum( [Hours Worked] *if(IsNull(Rate), 20, Rate) )

[#5]

Yes, looks like it's a problem in the level. The variable returns '-' in the table.

Going to need to work out another way to do it.

Thank you.