Table with Filter option in Qliksense

[abhijith28]

Hi All/ @sunny_talwar  @Kushal_Chawda 

I have a scenario but I am not sure whether  its possible or not in Qlik Sense.

Below is the data set.

But the output should be in  the below format.

In the above output.

Frequency of Name=2 and Frequency =1 

• Name = A has 2 different ID i.e. 1 and 2, so Frequency will be 1

Frequency of Name=3 and Frequency =2

• Name = B has 3 different ID i.e. 1,3 and 4 and also Name = C has 3 different ID i.e. 2,3 and 4

Frequency of Name=3 or More  and Frequency =2

• Name = C and D has 4 or more different ID

And if the user selects on "Frequency of Name" the data should be filtered accordingly

For ex: if i  select on "3 or More" the data should filter across the app.

Please help me on this scenario.

Please find the attached sample app.

Thanks,

[sunny_talwar · Accepted solution]

@abhijith28  try this

Dimension

=Aggr(If(Count(ID) > 3, '3 or More', Count(ID)), Name)

Expression

Count(DISTINCT Name)

[abhijith28]

@sunny_talwar 

Thanks for providing the solution😊

I have a new scenario. Sorry didn't mention it before.

There could be scenario where each "ID" will be associated with single "Name".

ID,Name

6,F

How to eliminate Frequency of Name = 1 in the dimension.

Output should be : Frequency of Name = 2, 3 and "3 or More".

Thanks,

[sunny_talwar]

@abhijith28 May be only include IDs where Name has Count(ID) > 1... so, dimension can stay the same, but change the expression to

Count(DISTINCT {<Name = {"Count(ID) > 1"}>} Name)

[abhijith28]

Hi @sunny_talwar 

In the table measure, below expression works fine.

Count(DISTINCT {<Name = {"=Count(ID) > 1"}>} Name)

But  i need to pass the dimension value in the filter pane as well with the values 2,3 and "3 or More".

What should be the expression for this case. 

Thanks,

[abhijith28]

@sunny_talwar 

I got the solution.

=aggr(
if(count(ID)>3,'3 or More',
if(count(ID)>1,count(ID)))

,Name)

Thanks for your HELP😊

[abhijith28]

HI @sunny_talwar 

Is there any way to keep the dimension constant--> 2,3, 3 or More 

even if the user selects single "ID" the dimensions will be fixed 2,3, 3 or More and Measure will be 0

Thanks,

[sunny_talwar]

Try this

=aggr(If(Count({<ID>} ID) > 3, '3 or More',
If(Count({<ID>} ID) > 1, Count({<ID>} ID)))

[abhijith28]

@sunny_talwar 

I Tried the below expression but no luck.

=aggr(
If(Count({<ID>} ID) > 3, '3 or More',
If(Count({<ID>} ID) > 1,
Count({<ID>} ID))),Name)

suppose if the user selects ID=4,5 and 6 it returns ID=3 or More and name=1

But the actual output should be ID=2,3,"3 or More" with Measures 0,0,1 

Thanks,

[abhijith28]

@sunny_talwar 

Using Table instead of Pivot table would work fine for this scenario. For Filtering, filter pane can be used for Table.

Thanks,