topic Re: Display values not part of a filter in App Development

Display values not part of a filter

MaxSDS — Sat, 11 Dec 2021 15:39:56 GMT

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)

Re: Display values not part of a filter

sasikanth — Sun, 12 Dec 2021 03:28:56 GMT

HI,

Try below expression to retrieve inactive employees

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

*Apply Aggr function if needed

Re: Display values not part of a filter

MaxSDS — Sun, 12 Dec 2021 18:26:35 GMT

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]}

Re: Display values not part of a filter

sasikanth — Mon, 13 Dec 2021 01:57:29 GMT

hi ,

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

Re: Display values not part of a filter

MaxSDS — Mon, 13 Dec 2021 10:03:48 GMT

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

Re: Display values not part of a filter

sasikanth — Tue, 14 Dec 2021 12:49:55 GMT

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