Not exists function in a Chart expression?

[#1]

     Hello, Is it possible to use a non exists function in the expression of a chart? I want to find the count of a particular key field if another field does not exist?

[Anonymous]

Hi,

Exists function can only be used in backend script. There are may ways to achieve this. If possible provide some more information to understand your question exactly.

[#3]

OK.

I want to create pie charts.

for e.g. There are 10 departments and 150 employees.One department may have multiple employees and one employee may work in multiple departments.. However, i want to display visually out of the whole lot,  the departments with no employees and the employeess which do not work in any department.

How can I achieve this in a pie chart or any other visual chart?

Thanks.

[jagan · Accepted solution]

Hi,

Try like this

Departments with No employees:

Dimension: Department

Expression: If(Count(Distinct EmployeeID) = 0, 1, 0)

This will list all deparments having no employees is displayed.

Employees with No Departments:

Dimension: EmployeeID

Expression: If(Count(Distinct Department) = 0, 1, 0)

This will list all employees having no departments is displayed.

If you want both in pie chart

Dimension : No dimensions to be used

Expression:

Departments with No employees =  Sum(Aggr(If(Count(Distinct EmployeeID) = 0, 1), Department))

Employees with No Departments = Sum(Aggr(If(Count(Distinct Department) = 0, 1), EmployeeID))

Regards,

Jagan.

[tresesco]

=Count({<Department=e({<Employee={'*'}>}Department)>} Department)       // No of departments with no employee

=Count({<Employee=e({<Department={'*'}>}Employee)>} Employee)            // No of employees with no department

[Anonymous]

Hi,

tresesco has already provided a best and recommended solutions here.

The "e" element in the script 1) excludes employees who already working in the department and counting those department without employee. 2) excludes departments which already have employees and counting those employee without department.

Thanks

[#7]

Thanks jagan.
Your set analysis worked like wonder.