set analysis by evaluating the variable value

[#1]

sum ( {$<$(Quintile)={3}>} SellPrice)

I am trying to evaluate a variable in a set analysis and if it is equal to 3, then sum the SellPrice.  It doesn't seem to work.  I think set analysis is expecting a dimension to be evaluate instead?  Any idea?

[swuehl]

You can think of set analysis like actually selecting on some fields. You don't select on a variable (well, depart from an inputbox drop list, I am talking of the kind of selections that limit the values in your fields, the selected, possible and excluded states), so this syntax you are using won't work.

You just want to do a check, if a variable's value is equal to a constant, then you want to sum a field.

So all you need is to write it like this:

= if( $(Quintile) = 3, sum(SellPrice) )

Hope this helps,

Stefan

[#3]

The if won't work because I want to get an aggregate of SellPrice based on the particular Quintile.  I have some sample data below. Summary data is what I am trying to get to.

PONumber	SellPrice	Quintile (variable)	Sum SellPrice based on Quintile
PO1	7,079	5	141,026
PO2	13,127	5	141,026
PO3	81,750	3	81,750
PO4	120,820	5	141,026
Total	222,776

Type	%	% Formula
Quintile1	0
Quintile2	0
Quintile3	37	=81,750/222,776
Quintile4	0
Quintile5	63	=141,026/222,776

[swuehl]

So you have up to 5 variables, Quintile1 to Quintile5? And the user can input some arbitrary values?

Where do you define the third column in your first table, where you link a Quintile variable to a PONumber?

Is this part of your data model?

[#5]

Quintile is the variable that evaluates various dimensions value and output the result of 1 through 5.  Based on that, I want to add the SellPrice for each one of the 5 Quintile so I can do a weighted average of the whole.

[swuehl]

Maybe similar to attached?

I created a simple variable vQuintile for your quintile, just for testing:

if(PONumber='PO3',3,5)

Then I created a data island with field Quintile (1 to 5) as dimension for your Quintile and as expression for the Percentage:

=Sum(aggr(if($(vQuintile)=Quintile,sum(SellPrice)),PONumber,Quintile))/sum(SellPrice)

Hope this helps,

Stefan