Set Analysis with if

[#1]

Hi All,

I am using following expression and it doesn't work as expected. Results are totally wrong.

=sum({$<[Year]={$(=Max([Retail
Year])),$(=Max([Retail Year],2))}>}IF([ABC Flag]<>1,[SalesA],[SalesB]))

I removed the set expression and moved the sum inside the if clause which worked fine.

what I meant is

IF([ABC Flag]<>1,sum([SalesA]),sum([SalesB])) works fine. how to make above expression work?

Thanks,

mc

[alexandros17]

It should have to work, are you sure you haven't columns named [SalesA], in that cases sum takes the column not the values of the rows.

let me know

[#3 · Accepted solution]

What if you evaluated the ABC Flag first like this:

IF([ABC Flag] <> 1, Sum({$<[Year]={$(=Max([Retail Year])),$(=Max([Retail Year],2))}>} [SalesA]),

Sum({$<[Year]={$(=Max([Retail Year])),$(=Max([Retail Year],2))}>} [SalesB]))

[#4]

Thank you so much Lior Abitbol . your solution worked perfectly.

I am not sure what I am trying to achieve is efficient or not, do you have any other suggestions?

What is the difference between using aggregate function inside the if clause vs outside if clause?

Thanks

[#5]

I don't have performance information on which approach is more efficient, but I don't think you should experience any performance difference by evaluating a condition first and calling an aggregate function (I do it this way all the time). If you want to make the expression more compact, you can define variables in the load statement for the aggregation part  (especially  if you are using it more than one place) and within your if statement call one of the variables based on the flag value.

Something like this:

In the load script:

SET vSumSalesA = Sum({$<[Year]={$(=Max([Retail Year])),$(=Max([Retail Year],2))}>}[SalesA]);

SET vSumSalesB = Sum({$<[Year]={$(=Max([Retail Year])),$(=Max([Retail Year],2))}>}[SalesB]);

In your UI:

IF([ABC Flag] <> 1, $(vSumSalesA), $(vSumSalesB))

[rbecher]

Hi

it's probably better to avoid the if( ) and do it like this:

Sum({$<[ABC Flag]-={1},[Year]={$(=Max([Retail Year])),$(=Max([Retail Year],2))}>} [SalesA]) +

Sum({$<[ABC Flag]={1},[Year]={$(=Max([Retail Year])),$(=Max([Retail Year],2))}>} [SalesB])

- Ralf

[#7]

Thank you all. Appreciate your help.