Set analysis without selection

[Anonymous]

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

[JonnyPoole]

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]

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]

Thanks Jonathan,

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

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

[Anonymous]

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 · Accepted solution]

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]

Hi Piet,

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

Thanks

BKC

[stigchel]

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]

Thanks you very much Piet. Well explained.

[stigchel]

Thank you and you're welcome.