Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
best practices for variables, expressions at page 23, 24, 26
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.
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
The fields are all in the same load file. So, where would I place the if statement?
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.