if without function(if)

[#1]

Hi,

Everytime I want to use an if statement, I have to surround it with a function (for example sum) even if I don't actually want the sum.

Take this for example

= 'Weighting = ' &
if(MetricIDWeights = 14 and InBucket = 6,

(Weighting)/ sum( if(
Enabled = 1 and InBucket = 6,
Weighting
)
))

qlikview doesn't like this

I have to write:

= 'Weighting = ' &
SUM(if(MetricIDWeights = 14 and InBucket = 6,

(Weighting)/ sum( if(
Enabled = 1 and InBucket = 6,
Weighting
)
)))

note the extra "sum()" in there around the "if" statement.

This doesn't work because qlikview doesn't support expressions(of(expressions())).

Any help?

[#2]

Hi,

Qlikview support expressions(of(expressions())). In that case you should look for the aggr() function.

Regards

Sridhar

[#3]

I don't follow how that solves the problem

I've looked in the manual, and thought this might be what I want, but it doesn't work.

= 'Weighting = ' &

sum(

if(MetricIDWeights = 14 and InBucket = 6,
aggr(sum(Weighting) / sum( if(Enabled = 1 and InBucket = 6,Weighting)))
)

)

[Oleg_Troyansky]

Alkaline,

can you describe in plain English what are you trying to calculate?

Oleg

[#5]

I want to calculate a percentage decimal from a set of weights.

For example, suppose I have a data set of weights: 1,2,3,4,5

my percentage for 1 would be 1/(5+4+3+2+1) that is 1/15 or 0.06, 2 would be 2/15 which is 0.12

Obviously the dataset size varies. The numbers are grouped by bucket, and each item is by MetricID.

MetricID, bucket, weight:
1, 1, 2,
2, 1, 3,
3, 2, 2,
4, 2, 3,

So I should be able to display for a particular ID, the weighting it has in a bucket.

so ID 1 in bucket 1 has a weighting of 2 out of 5 (because the only other item in bucket 1 has a weight of 3, so that's "2/(2+3)"

Effectively this weight will be displayed as a text label something like:
Metric 1 has weighting 40%

Then in another separate label:
Metric 2 has weighting 60%

And another:
Metric 3 has weighting 40%

And another:
Metric 4 has weighting 60%

Using the same code in each label, the %age weighting for a metric should be calculated based on the "numeric" weights given in the data.

I hope this is clear, it's a simple idea, but difficult to get across.

[Oleg_Troyansky · Accepted solution]

OK. If I understood you correctly, your formula should look like this:

sum(if (<condition1>, Weight)) / sum(if(<condition2>, Weight) )

For example:

sum(if(MetricID = 1, Weight)) / sum(if(Bucket = 1, Weight))

If you are using 8.5 and if your data set is large, you will be better off doing the same using Set Analysis, but for small data sets the formula above is just fine.

regards,

Oleg