Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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])
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) )
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])
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?
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) )
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.