13 Replies Latest reply: May 13, 2013 10:59 AM by Sukhwant Matharoo

# expression query

hi all,

i have a table

id     category     value         cat1

a        abc              10          abc

a         xxx          20             abc

a          yyy          30            abc

a          ddd          35            abc

i have a straight table and working on expression:

dimension          expression:sum(value)               expression:sum(value)-sum({<cat1={category}>}value)

id                         total                                             total-cat1

a                            95                                                  85

third column is not giving me 95-10 as value it gives me 95 in third column as well.

• ###### Re: expression query

In this case you need to use an if statement. The set analysis expression doesn't calculate the set on a row-by-row basis. Use sum(if(cat1<>category,value)) instead. See attached qvw.

• ###### Re: expression query

Gysbert,

the above expression also does not work ,

thanks

• ###### Re: expression query

same issue Gysbert in your qvw.

if you remove Category from Dimension, then 3rd column does not work

thanks

• ###### Re: expression query

Huh? What are you talking about? Category isn't a dimension at all in the straight table. There is one dimension and that is id. There are two expressions. The first is sum(value) and the second is sum(if(cat1<>category,value)). The straight table shows exactly the result as you posted in your first post.

• ###### Re: expression query

yes Gysbert,

Category is present in raw data, but in straight table only 3 columns

1 is dimension and 2 are expression

then the formula of If function does not work

thanks

• ###### Re: expression query

Dimension is id

Expressions:

1) Sum(value)

2) Sum(if(category <> cat1, value))

• ###### Re: expression query

Again... What are you talking about? Open the qvw I posted and take a good look at the straight table. It works just fine.

See above image. If that is not the result you want, please be clear about you do want. Because as far as I can tell this is exactly what you asked for in your first post.

• ###### Re: expression query

Hi Gysbert,

yes it works but just to give you additional info please find atatched image of raw data and desired data and the formula you gave does work

let me know

thanks

• ###### Re: expression query

See the attached file

hiii

see other file

are you looking for this

• ###### Re: expression query

mohit - if remove category, then does not work

• ###### Re: expression query

sum(if(category <> cat1, value))

Thanks,

Rocky

• ###### Re: expression query

hi rocky,

used the expression if(category<>cat1, sum(value),0)

still no desired result as per my post 2 table