topic Re: Simplifying an expression in QlikView

Simplifying an expression

Thu, 31 Jul 2014 20:11:37 GMT

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

Re: Simplifying an expression

maxgro — Thu, 31 Jul 2014 20:38:21 GMT

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

Best Practices for Data Modelling

Re: Simplifying an expression

Thu, 31 Jul 2014 20:40:36 GMT

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.

Re: Simplifying an expression

simenkg — Thu, 31 Jul 2014 22:10:53 GMT

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

Re: Simplifying an expression

Fri, 01 Aug 2014 17:38:28 GMT

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

Re: Simplifying an expression

Fri, 01 Aug 2014 17:58:27 GMT

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.