Filter Button to Filter down to Duplicate Values

[JustTryingToLearn]

Hi,

I am looking to create a button to filter down to duplicate values. I am working on a dashboard that tracks employee reviews. Employees should at most have 1 review attached to them - if they have more that is an issue. I need to make a filter button that, when clicked, filters down to the employees that have 2 or more reviews on a straight table. 

I am working with two fields - [Employee Name] and [Employee ID]. I have to use [Employee ID] when filtering because 2 different employees can have the same name (John Doe for example), but my straight table will only have employee name on it. 

What type of button should I use and what do you think my expression would be?

[stevejoyce]

Maybe a calculated dimension like this:

Dual('Duplicates', aggr(Only({<[Employee ID]= {"=Count(Distinct [Reviews])>1"}>} [Employee ID]), [Employee ID]))

[JustTryingToLearn]

This Doesn't seem to do anything at all - I should also have you know that I do not have a "Reviews" field (I used [Employee ID] again instead).

[JustTryingToLearn]

I tried this as well but it did not seem to work:

=only({$ <[Employee ID]={"=count([Employee ID])>1"}>} [Employee ID])

I am quickly losing hope that this is doable in qliksense.

[stevejoyce]

It is doable... Can you explain a little more specifically what you want.  Even a mockup in excel.  

A list box?  what are values in the listbox?

Table expression?  what are values in expression.

[JustTryingToLearn]

Sure thing:

I have a list that looks like this:

Employee ID	Fiscal Year	Plan Status
123456	2022	APPROVED
234567	2022	NON APPROVED
123456	2022	APPROVED
456789	2022	APPROVED
345678	2022	APPROVED
012345	2022	APPROVED
456789	2022	APPROVED
....	....	....

Notice that the Employee IDs '123456' and '456789' appears twice in this table. I want to make a button that filters this table to show only those two IDs - so I am really looking to be able to filter down to all values that appear more than once. After hitting the button I want to table to look like so:

Employee ID	Fiscal Year	Plan Status
123456	2022	APPROVED
123456	2022	APPROVED
456789	2022	APPROVED
456789	2022	APPROVED
....	....	....

As you can see, only the duplicate values appear in the table now. Again, I want to do this with a button action expression. I hope this helps!

[stevejoyce · Accepted solution]

Ok.  So what I would do is is make Employee ID a calculated dimensions defined like:

=Aggr(only({$ <[Employee ID]={"=count([Employee ID])>1"}>} [Employee ID]), [Employee ID])

and uncheck "Include null values"

If you want a toggle option available, you can have a variable input box with values 0 & 1.   "Filter to duplicates" with Values "Yes", "No", where yes = 1, no = 0.

The calculated dimension you just created can have a Show column condition if $(vDuplicateToggle) = 1.  And you can have the regular dimension for Employee ID in the tble as well with a Show column if $(vDuplicateToggle) = 0.