expression query

[#1]

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.

please advise if syntax in 3 expression is correct?

thanks in advance

[Gysbert_Wassenaar]

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.

[er_mohit]

See the attached file

hiii

see other file

are you looking for this

[#4]

mohit - if remove category, then does not work

[#5]

Gysbert,

the above expression also does not work ,

please advise

thanks

[boorgura]

Please try the expression:

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

Thanks,

Rocky

[#7]

same issue Gysbert in your qvw.

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

thanks

[Gysbert_Wassenaar]

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.

[#9]

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

please advise

thanks

[boorgura]

Dimension is id

Expressions:

1) Sum(value)

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