topic Re: Not exists function in a Chart expression? in QlikView

Not exists function in a Chart expression?

Fri, 04 Jul 2014 06:16:53 GMT

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?

Re: Not exists function in a Chart expression?

Anonymous — Fri, 04 Jul 2014 06:28:02 GMT

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.

Re: Not exists function in a Chart expression?

Fri, 04 Jul 2014 06:44:42 GMT

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.

Re: Not exists function in a Chart expression?

jagan — Fri, 04 Jul 2014 06:56:40 GMT

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.

Re: Re: Not exists function in a Chart expression?

tresB — Fri, 04 Jul 2014 07:05:07 GMT

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

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

Re: Not exists function in a Chart expression?

Anonymous — Fri, 04 Jul 2014 07:23:46 GMT

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

Re: Not exists function in a Chart expression?

Fri, 04 Jul 2014 07:38:05 GMT

Thanks jagan.
Your set analysis worked like wonder.