topic Re: Set analysis 'AND' & 'OR' in same expression in QlikView

Set analysis 'AND' & 'OR' in same expression

Mon, 20 Apr 2015 12:59:39 GMT

Hi,

Hoping someone can help.

Effectively I want to use a combination of logic in a single set analysis fomula. I understand that a comma is used in set analysis to get a result where both conditions are true (AND), and that a + is used for when either condition is true. Effectively, I want the amount to be summed when one condition (that yearvalue is current year) is always true, but when a condition is in a range of other values. Sample below.

=(SUM({$<dim_YearValue={$(=YEAR(TODAY()))}>,

<CustomerBusinessArea={'ba1'}>

+ <customerBusinessArea={'ba2'}>

+ <customerBusinessArea={'ba3'}>

+ <customerBusinessArea={'ba4'}>

+ <customerBusinessArea={'ba5'}>

+ <customerBusinessArea={'ba6'}>

+ <customerBusinessArea={'ba7'}>} AMOUNT))

Thanks in advance

Re: Set analysis 'AND' & 'OR' in same expression

jonathandienst — Mon, 20 Apr 2015 13:03:29 GMT

All you need is:

SUM({$<dim_YearValue = {$(=YEAR(TODAY()))},

CustomerBusinessArea={'ba1', 'ba2', 'ba3', 'ba4', 'ba5', 'ba6', 'ba7'}>} AMOUNT)

This will AND the two conditions (dim_YearValue and CustomerBusinessArea) . The CustomerBusinessArea condition will return true for any of the values in the list (OR)

HTH

Jonathan

Re: Set analysis 'AND' & 'OR' in same expression

Anonymous — Mon, 20 Apr 2015 13:06:16 GMT

I guess you need this:

sum(

{$<dim_YearValue={"$(=YEAR(TODAY()))"}, CustomerBusinessArea={'ba1','ba2','ba3','ba4','ba5','ba6','ba7'}>}

AMOUNT)

Re: Set analysis 'AND' & 'OR' in same expression

rubenmarin — Mon, 20 Apr 2015 13:06:20 GMT

Hi Dave, maybe intersection is what you're searching:

=(SUM({$<dim_YearValue={$(=YEAR(TODAY()))}>,

<CustomerBusinessArea*={'ba1','ba2','ba3'.....}>} AMOUNT))

After the *= QV will detect an error but on top of expression it says 'Expression OK', so it should work.

Re: Set analysis 'AND' & 'OR' in same expression

PrashantSangle — Mon, 20 Apr 2015 13:06:53 GMT

Hi,

Try below

=(SUM({$<dim_YearValue={"$(=YEAR(TODAY()))"},

CustomerBusinessArea={'ba1','ba2','ba3','ba4','ba5','ba6','ba7'}>} AMOUNT))

Regards

Re: Set analysis 'AND' & 'OR' in same expression

Mon, 20 Apr 2015 13:11:18 GMT

Thanks to everyone, Jonathan got there first.