expression

[upaliwije]

HI FRIENDS

I Have two following expression in my pivot table

sum(CL_AMT-RECOV-FORE)

sum(CL_AMT)-sum(RECOV)-sum(FORE)

Results of the two expression are different. second one gives me the correct result

Any reason for this ? pls let me know

[swuehl]

If the three fields are located in different tables in your data model, QV will use a temporary join to calculate first expression.

[upaliwije]

ALL DATA ARE IN SAME TABLE

[swuehl · Accepted solution]

If values are null,  record in first expression will contribute with Null. Try sum(rangesum(amount,-recov,-forecast))

[jagan]

Hi,

If all the three fields are in the same table, then both the expressions will match, if those are in different tables then they may or may not match, because of the joining may be some records will miss on the joining or when joined some records values may be null.  This are the possible chances hope this helps you.

Regards,

Jagan.

[jagan]

Hi,

If all data are in the same table then use Rangesum() like below because some of the values are null then when you do sum the sum will results null.

Rangesum(CL_AMT, -RECOV, -FORE)

OR

Sum(Alt(CL_AMT, 0), -Alt(RECOV, 0), -Alt(FORE, 0))

Regards,

jagan.