topic Wrong values using Flags in Sum Expression in QlikView

Wrong values using Flags in Sum Expression

Mon, 25 Apr 2011 15:51:52 GMT

Hi,

I'm developing an application witch values of 'Venda' and 'MetaR'. All values are from 2011, but soon I'll have from others years too.

I have a flag, 'AnoCorrente', to use inside the Sum expressions, to set only the values of the current year.

In the sum of 'Vendas', works fine, but in the Sum of 'MetaR', the values are unreallistic.

The expressions are:

=sum(AnoCorrente*[Venda R$ Fat Bruto])/1000

=sum(AnoCorrente*[MetaR R$ Fat Bruto])/1000

Any ideas of how to solve this problem ?

I've attached a file to show the issue.

Thanks!

Wrong values using Flags in Sum Expression

Oleg_Troyansky — Mon, 25 Apr 2011 15:58:40 GMT

I believe the problem is hidden in the data structure. You load 3 source data tables with numerous identical fields, into 3 separate tables. QlikView links the tables, generating 3 Synthetic keys. Your Flag is one of those identical fields, that become a key field.

I believe that if you concatenated all 3 tables into one, you'd get correct results and eliminate the need in 3 synthetic keys. So, try this:

DataTable:

LOAD .....

;

CONCATENATE (Data Table)

LOAD .....

;

Wrong values using Flags in Sum Expression

Mon, 25 Apr 2011 16:21:04 GMT

Oleg's answer is the most advisable to do, but you can also use the next expression:

SUM({$<AnoCorrente={1}>}[MetaR R$ Fat Bruto])/ 1000

It returns the same numbers as the expresion without AnoCorrente

Wrong values using Flags in Sum Expression

Mon, 25 Apr 2011 17:13:29 GMT

Hi could you explain the code {$<AnoCorrente={1}>} a little bit? what's $ here for?

Thanks

Wrong values using Flags in Sum Expression

Mon, 25 Apr 2011 17:26:28 GMT

Thanks ! It works !!

I cannot use Oleg's Solution, because other requirements, but this works just fine.

Thank you all, people!