8 Replies Latest reply: Sep 21, 2015 5:25 AM by Tresesco B

# Set Analysis

Hi There

I would like to write a set analysis expression which does the following.

Sum(Finance_Amount)- and ignore any selections made except The Year and the Month selections.

I have managed to do it using a few variables but I would like to see if there is a simpler way of doing it?

Regards,

• ###### Re: Set Analysis

Ignore all selections except some specific fields using Set Analysis

or

=Sum({1<

Year = {\$(=If(GetSelectedCount(Year) = 0,Chr(39) & '*' & Chr(39),Concat (Distinct Chr(39) & Year & Chr(39),',')))},

Month = {\$(=If(GetSelectedCount(Month) = 0,Chr(39) & '*' & Chr(39),Concat (Distinct Chr(39) & Month & Chr(39),',')))}

>}Finance_Amount)

• ###### Re: Set Analysis

Try like:

Sum({1<Year=p(Year), Month=p(Month)>}Finance_Amount)

• ###### Re: Set Analysis

Thank You, Much simpler

• ###### Re: Set Analysis

This will not work, because the p(Year) can be influenced by other selections.

Say if a company has only sold a product in 2014, then selecting that product will reduce p(Year) to only 2014.

You should use the [Year]=\$::[Year] syntax as it will only use the explicit selection for Year.

• ###### Re: Set Analysis

Thanks bwisenosimenkg, you have a valid point. I had an impression that :: is meant for Alternate State and \$:: is used for default state, didn't notice it works for Current Selection as well. Thank you once again. I was trying to play with this  approach to check if by chance we are mixing it up, but not so far. It seems yours to be right.

• ###### Re: Set Analysis

Try:

Sum({<Year = Max(Year),Month = Max(Month) Region =,Product=>}Finance_Amount)

• ###### Re: Set Analysis

ex

Sum({<Year={"=\$(Max(Year))"},Month={"=\$(Max(Month))"}, Dim1=,Dim2,Dim3>}Finance_Amount)

hth

Sasi

• ###### Re: Set Analysis

HI Rido

may be this

Sum({1<[Year]={'2015'}, [Month]={'April'}>}Finance_Amount)

Or use Max(Year), Max(Month) for Current Month & Year

• ###### Re: Set Analysis

There is a syntax for this, and it is as follows:

Sum({1<[Year]=\$::[Year], [Month]=\$::[Month]>} Finance_Amount)

The 1 says that the expression should take the values from the unfiltered set, so it disregards all selections.

The [Set]:: syntax says that the expression should take the values from the specified Set for that field. \$ is the current selections Set.

Hope this works for you

Regards
SKG