Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

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

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Set analysis without selection

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
Highlighted
Employee
Employee

Re: Set analysis without selection

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

Highlighted
Partner
Partner

Re: Set analysis without selection

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

Highlighted
Specialist II
Specialist II

Re: Set analysis without selection

Thanks Jonathan,

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

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

Highlighted
Specialist II
Specialist II

Re: Set analysis without selection

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

Highlighted
Partner
Partner

Re: Set analysis without selection

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

Highlighted
Specialist II
Specialist II

Re: Set analysis without selection

Hi Piet,

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

Thanks

BKC

Highlighted
Partner
Partner

Re: Set analysis without selection

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


Highlighted
Specialist II
Specialist II

Re: Set analysis without selection

Thanks you very much Piet. Well explained.

Highlighted
Partner
Partner

Re: Set analysis without selection

Thank you and you're welcome.