Display values not part of a filter

[MaxSDS]

In my app I have 2 tables: Employees, Worked hours linked by employee's ID.

In worked hours I have a [date], [entry time], [exit time] and [worked hours].

I created 2 filter fields: Year and month (created as autocalendar of [date])

On one table I display: Employees' name, sum([worked hours])

On a KPI i display total number of active employees in the selected Year & Month(s) => Count(DISTINCT [Employee Name])

What I struggle to achieve is to get a table with the list of employees who were not active (zero hours worked) in the selected Year & Month(s)

[sasikanth]

HI, 

Try below expression to retrieve inactive employees

=Count({ DISTINCT<[Employee Name]={"=sum([worked hours])<=0"}>}  [Employee Name])

*Apply Aggr function if needed

[MaxSDS]

Thanks for the reply but this doesn't work not even to get the number of "inactive" employees.

BTW getting the count was easy and I achieve it with

=Count({1<[Employee name]={"*"}>} [Employee name])-Count( [Employee name])

The real challenge is get the LIST of name.

I tried several combination of Aggr function but the issue is (I believe) that when the results are being displayed then they filtered again.

This formula still return ONLY the list of active ee and not the full list ... as I was expecting ...

=Aggr(Only({1<[Employee name]={"*"}>} [Employee name]), [Employee name])

I even created a separate table (not linked) with the full list of ee [Employee name full_list] but I can't find a way to "exclude" the active from that list ... something like

[Employee name full_list] - [Employee name]

[Employee name full_list] -= {[Employee name]}

[sasikanth]

hi , 

Would you be able to share some sample data of two tables??

[MaxSDS]

Sure, here we go.

In November EE_ID=3 ("John Pau") should be listed in the "inactive" EE, while filtering on December you should get EE_ID=2 ("Jerry Smith").

If you filter with month=October in the list of  "inactive" EE you should get all 3 EE.

My target way to display these info would be a table with, as dimension, the list of "inactive" EE

let me know if it's not clear enough

thanks

[sasikanth · Accepted solution]

Hi, 

there should be at least one entry per granularity level (Year/Month/Week/Day) to display non recorded values in tables, 

 since EE_ID =3 related records are not recorded for the entire month of Nov, Qilk cannot show  EE_ID=3 for this month  

One possible solution would be ,

Dimension : EE_ID

Expression1: Active Hours     = sum({ 1-$} 0)+ sum(Worked_hours)

Expression 2 (Status):  =if(sum( Worked_hours)>0,'Active','Inactive')

Thanks,

Sasi