if vs set

[#1]

Hi, I have read in various threads that set should be faster and give better performance than if.

I have a case where I just need to say if FieldA = 'x value' then for instance multiply field B, field C and Field D.

It seems silly to make a set for all fields, so I would expect an if solution to be the choice?  

[Michiel_QV_Fan]

Can you elaborate a bit more. Set is used for creating variables and if is a control statement. What do you mean by set in relation to your question?

[Peter_Cammaert]

I think he means Set Analysis and the IF() function.

Set Analysis and IF() are not always interchangeable. Set Analysis will reduce the data that goes into your expressions (in a very efficient way) while IF() may do different things depending on what data is offered. An IF() with an else parameter won't reduce the data, however.

[#4]

oh sorry, as Peter assume, yes I do mean set analysis vs. if() .

Also I forgot to ask how ?

if([Field A] = 'text', sum([field B]/sum([field C]/[fieldD]))) does not work.

[Peter_Cammaert]

Let's do this step by step.

First, why isn't it working? Is [Field A] a dimension in your table? Do the values [Field C] and [Field D] belong to the same row?

In expressions, it is important whether you place an IF() inside or ouside the aggregation functions. To figure out what will work, we need more information about your Data Model. Can you supply some?

[#6]

thanks, I dont think that I am allowed to share details, but I will try to describe. I had to change it a bit and insert a variable (which again build on a set). I have a star model, they should be connected.

if([Field dimA] = 'text', (sum([Field fact A]/([$(variable1)])/sum([Field dimB])))

The result is '-'

[jonathandienst]

How is 'variable1' constructed and what does it look like?

[#8]

The variable is just a similiar If/set statement, though in the long it will have multiple criteria, so I put it in a variable to make it simpler. I have replaced it with a fixed number to be sure that is not the problem.

So the overall formula is (X/Y)/Z. Where Y at the moment is set to 200500100.

So if([Field dimA] = 'text', (sum([Field fact A]/200500100)/sum([Field dimB]))), they are from different tables connected indirectly via the keys.

In Excel the criteria/if/set formula could look like this for X could look like this =SUMIF(C:C;"=text";E:E). So I just want to search in one field, and if criteria is true, then sum correspondent rows in another field.

Further more I have tried Aggr, but I have got any sense out of it so far.

[rajeshvaswani77]

The set would be more efficient than if in case of large data.

thanks,

Rajesh Vaswani

[#10]

Thanks, I got it to work with set analysis, though the number it extremely low, from what to expect.

The set version without variable goes like this:

=(Sum({$<[Field dimA] = {'text'}>} [Field fact A])

/sum({$<[Field A] -= {'text2'}>} [Field fact B]))  //this is the variable from above = 200500100

)/Sum(Sum({$<[Field dimA] = {'text'}>} [Field dimB])

How would it look like as an if formula?