topic Re: How to - Multiple conditions in set analysis in QlikView

How to - Multiple conditions in set analysis

rmueller — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am having a problem with the syntax for set analysis using two different conditions

I have the following working; it sums the [Ins. SL Fol Up Expected Amount] based on a date condition and a selected offset - works perfect

sum(

{

[Ins. Fol Up Next Action Date]={"$(='<'& Only(Date(AsOfDate_E_Orig-(Pick(WildMatch(_FollowUpWalkback, '0 Days','30 Days','60 Days','90 Days','120 Days','150 Days','180 Days','*'),0,30,60,90,120,150,180,0)))))"}

>} [Ins. SL Fol Up Expected Amount])

I'd like to add another condition that restricts the set to the date condition above as well as all records that "contain" a specific set of string value(s) within a field

The string field is a comma separated list - [COB List]

Example: REVUNDER, CO45, PR01

I have a list box to select specific codes found in the [COB List]. That populates a variable call SelectedCobCodes.

So I'm interested in rows that have any of the selected items (stored in the variable SelectedCobCodes) in the list box found within the field [Ins. SL Fol Up Cob Codes]. Ideally, if SelectedCobCode was a list from multiple selections within the list box, I'd like the index function or whatever function needs to be used to return a value greater than zero when any or all values can be found in [Ins. SL Fol Up Cob Codes]. I figured out the following only gives me results for one SelectedCobCode selected within the list box

=index([Ins. SL Fol Up Cob Codes],SelectedCobCode) > 0

Two things...

1. How can i return a result >0 when any or all of my selected values are found

2. How do I integrate that criteria into my set analysis

thanks

ron

Re: How to - Multiple conditions in set analysis

sunny_talwar — Fri, 07 Oct 2016 18:23:38 GMT

Would it be possible to share a sample to look at this before I propose anything?

Re: How to - Multiple conditions in set analysis

rmueller — Fri, 07 Oct 2016 18:34:04 GMT

I not sure how to do that, this is part of a huge app...

To be clearer on the need.

ListBox of [Cob Code]

CO45

PR01

PR104

Chart straight table column [Ins. SL Fol Up Cob Codes] example

PR01,CO45

PR01

CO45,PR02,PR104

PR104

So, if i select CO45 and PR01 from the list box; I'll then end up with CO45, PR01 in my variable called SelectedCobCodes (the variable populates via =GetFieldSelections( [COB Code]) )

I would need to select only those rows in the chart straight table that contained either CO45 or PR01; in the example, row 1, 2, 3 would qualify.

Hope that makes is clearer.

thanks

ron

Re: How to - Multiple conditions in set analysis

sunny_talwar — Fri, 07 Oct 2016 18:50:47 GMT

Why don't you create a link table in the script to create [Cob Code]

LinkTable:

LOAD [Ins. SL Fol Up Cob Codes],

SubField([Ins. SL Fol Up Cob Codes], ',') as [Cob Code]

Resident .....;

Re: How to - Multiple conditions in set analysis

jduenyas — Fri, 07 Oct 2016 21:19:08 GMT

your expression is wrong:

[Ins. Fol Up Next Action Date]={"$(='<'& Only(Date(

normally it should be {< [fieldname]={">=$(=Function(bla bla )) <=$(=function(bla bla bla)) "} >}

Please revisit your set analysis setup. (and count parenthesis to close all...)

Re: How to - Multiple conditions in set analysis

rmueller — Mon, 10 Oct 2016 17:46:04 GMT

Thanks Sunny - your suggestion was perfect, works like a charm!