Why is my chart treating negative values as positive values?

[#1]

I have an expression which give a positive or negative value depending on whether a field PURCHASE_SALE is 'P' or 'S'

IF(PURCHASE_SALE='P',SUM(PRICE*NOMINAL/-100),SUM(PRICE*NOMINAL/100))

When displayed in a straight table and selecting the Total Mode and 'Sum of Rows' i get the expecting total displayed.

However when using the same expression in a bar chart (the only dimension is calendar week), it seems to ignore the fact half the values are negative and adds them all up as if they were positive. Wyy is this?

How can i get my bar chart to treat the negative as negative vaules?

[Anonymous]

If statement will work only when the specific field is available in the dimension.

load*,

IF(PURCHASE_SALE='P',SUM(PRICE*NOMINAL/-100),SUM(PRICE*NOMINAL/100)) as Measure

from <table name>

group by <field names>

In fact you can calculate this at script level and just use sum(Measure) as expression,

it will solve your issue...

Regards

Nitin

[MK_QSL]

Can you upload your sample file?

[kumarnatarajan]

HI,

Include over all sum. then you will get currect total.

Ex:

Sum(IF(PURCHASE_SALE='P',SUM(PRICE*NOMINAL/-100),SUM(PRICE*NOMINAL/100)))

[#5 · Accepted solution]

Actually, Kumar that didn't work, but this did. I didn't need the SUMs within the IF statement

SUM(IF(PURCHASE_SALE='P',PRICE*NOMINAL/-100,PRICE*NOMINAL/100))