How can I use Min and Max functions in a calculation

[diffeyj]

Hi All,

I have a table for staff growth. The table itself appears to be working fine. It is pretty simple, I have a list of periods accross the bottom, then a total of employees that were at the organisation during the month. I have double checked the figures and they are correct.

The issue I am having is that I want a label, preferably the title of the graph which gives me as a percentage the growth. So if I start the year with 50, end with 55, then I have grown 10%. At the moment I can't get the calc to work because the min and max functions are giving me issues.

If I have a label, which has nothing but =min(YearStart) I get the correct minimum selected year start date. If I use it within a function, so something like =sum(if(EmployeeStartDate<min(YearStart) and (EmployeeTerminationDate>min(YearStart) or EmployeeTerminationDate=Null()),1,0)) I am getting a "-"

Attached is a screenshot of the issue I refer to. Note that the min and max functions are working in the labels, but not in the calculations

If anyone could tell me where I am going wrong, that would be great.

cheers

[pover]

Try the following set analysis:

count({$<EmployeeStartDate={"<=$(=min(YearStart))"}, EmployeeTerminationDate={">=$(min(YearStart))"} + {}>} EmployeeID)

Regards.

[diffeyj]

very close. I need to have it check that the

{EmployeeStartDate <= YearStart} and {EmployeeTerminationDate >= YearStart or EmployeeTerminationDate = null() }

I will be honest, I don't understand the entire analysis you have, and so I am not really sure how to add in the OR part, as an AND appears to be a comma.

I also don't understand the extra + {}> in the termination date part.

thanx for any help provided

[diffeyj]

can anyone else assist with this one?

[#5]

hI Joshua Diffey

I think, take two variables and assign the min and max values to it preor than to your expression. then use those variables name into the expression. It may helps you.

[pover]

Joshua,

count({$<EmployeeStartDate={"<=$(=min(YearStart))"}, EmployeeTerminationDate={">=$(=min(YearStart))"} + {}>} EmployeeID)

I forgot an '=' sign on the second min. The {} is for the termination dates that are null although I don't know if you have the value as null or an empty string. In theory it should give you what you want and you said it was close, so what was it still not filtering?

Regards.

[diffeyj]

Hi Karl,

Thanx for the reply. I can confirm that the issue appears to be where the termination date is blank. I have 18 staff members that have a start date before the start of the year, out of those 6 have not been terminated. I should be getting the number 18, but instead I am getting the number 12.

I can also confirm that when the Employee is not terminated the EmployeeTerminationDate is null (I have used the isNull function to double check).

Is there any extra info I can provide that could help. Because this file has payroll info in it, I am not really allowed to publish it.

regards

josh