if statement - different result

[Anonymous]

Hi,

These two statements should give the same result, but don't. I'm using a scatter-chart.

Can anyone explain why the two statements return different results? It works with the second version

1) if(Nr = 1, sum({$<Year={2008, 2009}>} AMOUNT_LOCAL), sum({$<Year={2008, 2009}>} AMOUNT_EURO)) / Sum({$<Year={2008, 2009}>} QUANTITY)

2) sum({$<Year={2008, 2009}>} if(Nr = 1, AMOUNT_LOCAL, AMOUNT_EURO)) / Sum({$<Year={2008, 2009}>} QUANTITY)

regards

Nils

[johnw · Accepted solution]

I assume your data looks something like this?

Year, Nr, AMOUNT_LOCAL, AMOUNT_EURO, QUANTITY
2008, 1, 2000, 5000, 200
2009, 0, 3000, 4000, 400

For this, you want (2000 + 4000) / (200 + 400) = 10?

The difference in your expressions is WHEN they evaluate Nr. The first evaluates it ONCE for the entire table. Since the table contains values of Nr that are not 1, the condition is false, so ALL rows will be in AMOUNT_EURO. That is not what you want.

The second expression evaluates NR on a row-by-row basis. So it will realize that the first row should take AMOUNT_LOCAL, and the second row should take AMOUNT_EURO.

Untested, but I am guessing that the following is a more efficient way to get the same result. In this expression, set analysis splits your selected data into two groups, the first for Nr=1 and the second for all other values of Nr. It can then do a sum for each group individually without having to check the NR for every row.

(sum({<Year={'2008','2009'},Nr*={'1'}>} AMOUNT_LOCAL)
+sum({<Year={'2008','2009'},Nr-={'1'}>} AMOUNT_EURO))
/sum({<Year={'2008','2009'}>} QUANTITY)

[Anonymous]

Hi,

Thanks for your reply. "Nr" is in a seperate tabel with only two records - one with NR=1, and one with NR=2. The other fields are in one table.

But, your explanation seems right. This could also explain why the result sometimes was correct - and other times incorrect

regards

Nils