Using set analysis on multiple condition for a single column

[Iwin]

Hi all,

I want to perform set analysis on multiple fields.

Eg for a  single column Revenue ,it is calculated as follows :

if(class_Category='bill_to_Rev' and CLASS_CODE = 'BILL_EQUAL_TO_REV',sum(sales amount)  else

sum({<class_Category='Sales'>}sales) -sum({<class_Category='Sales'>}costofgoods)/sum({<class_Category='Sales'>}sales) )

How can i put this in single set analysis expression?

[Iwin]

any way to put this expression in single set analysis?

[Sammy_AK]

What do you mean by "Put this expression in single set analysis" ? are you referring that above mentioned expression with if should be substituted in a set analysis expression ?

[Iwin]

i want to show the following,

if condition of  class_Category='bill_to_Rev' and CLASS_CODE = 'BILL_EQUAL_TO_REV' is satisfied,show

sum(sales amount)

if doesnt satisfy,

sum({<class_Category='Sales'>}sales) -sum({<class_Category='Sales'>}costofgoods)/sum({<class_Category='Sales'>}sales) )

I tried using if but didnt work.

[Sammy_AK]

try this:

if(class_Category='bill_to_Rev' and CLASS_CODE = 'BILL_EQUAL_TO_REV'

,sum(sales amount) // True staement

,(sum({<class_Category='Sales'>}sales) -sum({<class_Category='Sales'>}costofgoods))/sum({<class_Category='Sales'>}sales) //False statement

[Iwin]

I had tried this but value was coming correctly only when any month was explicitly selected,else value was coming as 0.

What might be causing this?

[Sammy_AK]

I beleive the parameters used in set analysis do not return a result when you do not select a month.

Either the aggregation column would have string or garbage values in it.  Worth checking the sales column and correct it (i.e. standardizing values under sales are all numeric.)

Or you will have to use more set analysis parameters / filters in cyour expression so that these garbage / string values are filtered.