Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set analysis without selection

Hi Experts,

My dimension is [Defect Type]

I want to show Defect Cause in textbox (Top 3) Note : Top 3 Defect Cause I am deciding based on defect count for each [Defect Type] and Defect Cause.

So my [Defect Type] are (Testing, Requirement, Development etc. )

So for each [Defect Type] I should show three text boxes

ex . for Requirement

Textbox1 should show 'CR-Change/Missing Requirement

Textbox2 should show 'NC- Not clear Requirement

Textbox3 should be black as I dont have third value(record)

Note : This should work without any selection

Hope I am clear on my requirement.

Also attaching my sample application

Thanks

BKC

1 Solution

Accepted Solutions
stigchel
Partner - Master
Partner - Master

Ok, then I think you can use the concat function with the sort option in combination with subfield like this:

=SubField(Concat({<[Defect Type]={'Requirements'}>} [Defect Cause],'@',1/[Defect Count]),'@',1)

=SubField(Concat({<[Defect Type]={'Requirements'}>} [Defect Cause],'@',1/[Defect Count]),'@',2)

=SubField(Concat({<[Defect Type]={'Requirements'}>} [Defect Cause],'@',1/[Defect Count]),'@',3)

View solution in original post

12 Replies
JonnyPoole
Employee
Employee

Make sure the SET OPERATOR for [Defect Count] is also filtered for the specific Defect Count you are after or it won't work. For example:

change:

=only({<[Defect Type]={'Requirements'},[Defect Count]={$(=max([Defect Count]))}>}[Defect Cause])

to:

=only({<[Defect Type]={'Requirements'},[Defect Count]={$(=max( {<[Defect Type]={'Requirements'}>} [Defect Count]))}>}[Defect Cause])

stigchel
Partner - Master
Partner - Master

You can use Max(...,1) and Max(..,2) like

=Concat({<[Defect Count]={'$(=Max({<[Defect Type]={'Requirements'}>}[Defect Count],1))'}>} [Defect Cause])

=Concat({<[Defect Count]={'$(=Max({<[Defect Type]={'Requirements'}>}[Defect Count],2))'}>} [Defect Cause])

=Concat({<[Defect Count]={'$(=Max({<[Defect Type]={'Requirements'}>}[Defect Count],3))'}>} [Defect Cause])


I've used concat as theoratically there can be records with the same (max) defect count

Anonymous
Not applicable
Author

Thanks Jonathan,

How to find second max and third max in your approach .

ex : i have defect count 10,3,6,15

Anonymous
Not applicable
Author

Hi Piet,

Thanks for reply . your approach is working fine till dont have same (max) defect count.

Is there any way to select 5,5,5 in all three max if I have 5,5,5,5,5,5 in defect count

Thanks

BKC

stigchel
Partner - Master
Partner - Master

Ok, then I think you can use the concat function with the sort option in combination with subfield like this:

=SubField(Concat({<[Defect Type]={'Requirements'}>} [Defect Cause],'@',1/[Defect Count]),'@',1)

=SubField(Concat({<[Defect Type]={'Requirements'}>} [Defect Cause],'@',1/[Defect Count]),'@',2)

=SubField(Concat({<[Defect Type]={'Requirements'}>} [Defect Cause],'@',1/[Defect Count]),'@',3)

Anonymous
Not applicable
Author

Hi Piet,

Solution is working fine . Could you please explain .. what this expression is doing . What does '@' means ?

Thanks

BKC

stigchel
Partner - Master
Partner - Master

Sure,

There are a three elements to the function Concat:

concat([{set_expression}]  [ distinct ] [ total [<fld {, fld}>]] expression[, delimiter[, sort_weight]])


In the above example I'm concatenating a string with delimiter @ with a sort weight of 1 divided by the defect count. The @ is just chosen as a character not normally present in a string, as I need it later in the subfield function to split the string in field values again.

The 1 divided by the defect count is for the sort weight to be descending, so the concatenated string is build up with defect causes with a greater count first


subfield(s, 'delimiter' [ , index ] )


As said the subfield function just picks the field values from the string with delimiter @ using an index


Anonymous
Not applicable
Author

Thanks you very much Piet. Well explained.

stigchel
Partner - Master
Partner - Master

Thank you and you're welcome.