topic Re: Set analysis without selection in QlikView

Set analysis without selection

Anonymous — Mon, 09 Mar 2015 14:34:59 GMT

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

Re: Set analysis without selection

JonnyPoole — Mon, 09 Mar 2015 14:47:30 GMT

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

Re: Set analysis without selection

stigchel — Mon, 09 Mar 2015 15:03:07 GMT

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

Re: Set analysis without selection

Anonymous — Tue, 10 Mar 2015 05:40:21 GMT

Thanks Jonathan,

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

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

Re: Set analysis without selection

Anonymous — Tue, 10 Mar 2015 05:42:46 GMT

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

Re: Set analysis without selection

stigchel — Tue, 10 Mar 2015 12:26:13 GMT

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)

Re: Set analysis without selection

Anonymous — Thu, 12 Mar 2015 07:40:14 GMT

Hi Piet,

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

Thanks

BKC

Re: Set analysis without selection

stigchel — Thu, 12 Mar 2015 09:56:34 GMT

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

Re: Set analysis without selection

Anonymous — Thu, 12 Mar 2015 11:18:57 GMT

Thanks you very much Piet. Well explained.

Re: Set analysis without selection

stigchel — Thu, 12 Mar 2015 12:05:32 GMT

Thank you and you're welcome.

Re: Set analysis without selection

Anonymous — Fri, 13 Mar 2015 10:17:47 GMT

Hi Piet,

Sorry to disturb you again.

Above expression is working fine in text box but when I am using same expression in listbox ... it not working.

Could you please help me on this .

Thanks

BKC

Re: Set analysis without selection

stigchel — Fri, 13 Mar 2015 11:02:16 GMT

I'm assuming you want a listbox displaying the top 3 Defect causes for a specific type?

In a listbox you always select values from a field, not just some strings. The above solution results in a string and can not be used in a listbox. You can use an expression to limit the values from a field and display those in a listbox. In a listbox choose expression instead of a field and use something like

=Aggr(if(Rank([Defect Count])<4 and [Defect Type]='Requirements',[Defect Cause]),[Defect Cause])

It uses the rank function, see help and also attached example might help (see top 10 calculated listbox)

Re: Set analysis without selection

Anonymous — Fri, 13 Mar 2015 13:57:08 GMT

Thanks Piet