Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Table with Filter option in Qliksense

Hi All/ @sunny_talwar  @Kush 

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

Below is the data set.

abhijith28_0-1602759973439.png

But the output should be in  the below format.

abhijith28_1-1602760633435.png

 

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,

 

 

Labels (1)
2 Solutions

Accepted Solutions

@abhijith28  try this

Dimension

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

Expression

Count(DISTINCT Name)

View solution in original post

@abhijith28 Try to change the expression to

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

or

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

 

View solution in original post

13 Replies

@abhijith28  try this

Dimension

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

Expression

Count(DISTINCT Name)

View solution in original post

Creator II
Creator II

@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,

 

@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)

 

Creator II
Creator II

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,

Creator II
Creator II

@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😊

Creator II
Creator II

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,

Try this

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

@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_1-1603115542132.png

 

 

 

Creator II
Creator II

@sunny_talwar 

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

abhijith28_0-1603117484318.png

Thanks,