topic Re: Calculation Syntax in QlikView

Calculation Syntax

Anonymous — Tue, 24 Nov 2015 11:22:25 GMT

Hello,

I know computers follow a simple logic and usually the user causes the error but Im totally irritated

I try to do a simple calculation but QV seems to find a way I have not thought about.

I get different results when excluding a certain file in a sum-function with Set Analysis and when subtracting the sum of that value.

sum({$<CurrentYearFlag={1}, Incoterm-={FCA}>}[Total Invoice Amount])

= 10.113.047

sum({$<CurrentYearFlag={1}>}[Total Invoice Amount])-sum({$<CurrentYearFlag={1}, Incoterm={FCA}>}[Total Invoice Amount])

= 13.467.336

Can someone explain me what could be the problem with my data / Syntax that leads to different results?

Many Thanks!

sunny_talwar — Tue, 24 Nov 2015 13:23:37 GMT

Have you made any selections in Incoterm field when you tried to do this?

There is a difference between

Incoterm-={FCA} and Incoterm = {*} - {FCA}

Your second expression is more like this -> Incoterm = {*} - {FCA}

Anonymous — Tue, 24 Nov 2015 13:27:30 GMT

No selections have been made.

Anonymous — Tue, 24 Nov 2015 13:33:30 GMT

Ok, I think I found the reason.

The difference between the two formulas:

sum({$<CurrentYearFlag={1}, Incoterm-={FCA}>}[Total Invoice Amount])

sum({$<CurrentYearFlag={1}>}[Total Invoice Amount])-sum({$<CurrentYearFlag={1}, Incoterm={FCA}>}[Total Invoice Amount])

is exactly the amount of empty fields I got in [Incoterms].

But this does still not explain the two different results to me.

sunny_talwar — Tue, 24 Nov 2015 13:33:33 GMT

Still what do you get for this -> Sum({$<CurrentYearFlag={1}, Incoterm= {'*'} - {'FCA'}>}[Total Invoice Amount])

Anonymous — Tue, 24 Nov 2015 13:35:56 GMT

same as

sum({$<CurrentYearFlag={1}, Incoterm-={FCA}>}[Total Invoice Amount])