topic Re: set analysis by evaluating the variable value in QlikView

set analysis by evaluating the variable value

Thu, 01 Mar 2012 23:59:45 GMT

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?

set analysis by evaluating the variable value

swuehl — Fri, 02 Mar 2012 00:11:21 GMT

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

Re: set analysis by evaluating the variable value

Fri, 02 Mar 2012 00:39:29 GMT

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

set analysis by evaluating the variable value

swuehl — Fri, 02 Mar 2012 00:48:25 GMT

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?

Re: set analysis by evaluating the variable value

Fri, 02 Mar 2012 00:52:12 GMT

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.

Re: set analysis by evaluating the variable value

swuehl — Fri, 02 Mar 2012 01:27:58 GMT

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