Simplifying an expression

[#1]

Good afternoon.

I am looking to simplify the following expression.  Is the best practice to create a variable, set analysis, etc?

If([Group Volume/Non Volume]='Volume Driven',([CM BUD SAL/UNIT]-[CM ACT SAL/UNIT])*[CM ACT VOL], [CM BUD SAL]-[CM ACT SAL])

I have several of these expressions and they currently work, but thinking there is a better way to do it.

Thanks,

Josina

[maxgro]

best practices for variables, expressions at page 23, 24, 26

Best Practices for Data Modelling

[#3]

1.The best method I would suggest here is doing this on the Back-end if possible.It will be the same expression, but all these fields need to be in the same table. Like

If([Group Volume/Non Volume]='Volume Driven',([CM BUD SAL/UNIT]-[CM ACT SAL/UNIT])*[CM ACT VOL], [CM BUD SAL]-[CM ACT SAL]) as ReqField

Then on the front-end expression sum(ReqField)

2.Second method is doing [Group Volume/Non Volume]='Volume Driven' on back-end (again need to be in the same table)

If([Group Volume/Non Volume]='Volume Driven',1) as Flag

Then on the front-end

Sum(If(Flag=1,([CM BUD SAL/UNIT]-[CM ACT SAL/UNIT])*[CM ACT VOL], [CM BUD SAL]-[CM ACT SAL])

3. Similarly doing the Then,else piece on the back-end and using that calculated field name on the front end.

[simenkg]

How about:

sum({$<[Group Volume/Non Volume]={'Volume Driven'}>}([CM BUD SAL/UNIT]-[CM ACT SAL/UNIT])*[CM ACT VOL])

+

sum({$<[Group Volume/Non Volume]-={'Volume Driven'}>}[CM BUD SAL]-[CM ACT SAL])

If this is done row by row, then you should do these things in the script.

As Ajay writes, do the calculations in the load as

If([Group Volume/Non Volume]='Volume Driven',[CM BUD SAL/UNIT]-[CM ACT SAL/UNIT])*[CM ACT VOL]),[CM BUD SAL]-[CM ACT SAL]) as SAL,

Then your expression is simplified to Sum(SAL)

Hope this helps

Regards

SKG

[#5]

The fields are all in the same load file.  So, where would I place the if statement?

[#6]

If the fields are all in the same table(say called Accounts), then the If() expression can be added to that table, on the back-end.