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

[#1]

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

[jonathandienst · Accepted solution]

Hi

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

[Anonymous]

I guess you need this:

sum(

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

AMOUNT)

[rubenmarin]

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.

[PrashantSangle]

Hi,

Try below

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

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

Regards

[#6]

Thanks to everyone, Jonathan got there first.