Aggr or Set Analysis

[#1]

Hi there,

I'm trying to do a calculation in an expression in order to aggregrate some cash details in the same currency.

You'll find enclosed an example of what i'm trying to do:

I have some data for different account and in different currency. I'm trying to aggregate those data in a single currency which can be chosen by the user. I don't want to make this calculation during the load because in a shortterm view i will need to get the more flexibility as possible in order to make some comparison, variation, ...

So i really need to do this in an expression and not in the load.

I try do do it using either set analysis or the Aggr function but as i'm new with qlikview i didn't succeed.

Can someone help me please.

Thanks & regards,

[#2]

The expression i'll try to use is something like :

=sum(PNL*if(CURRENCY=ToCur,1,sum({<FxDate=DATE, FromCur=CURRENCY>} FX)

but still have an error in it.

thks

[#3 · Accepted solution]

Hi,

I see the problem. It is due to the way If Conditions are evaluated. If the fields are from two different tables, there is a join made before doing the evaluation, so a simple if statement returns you a multiple of the value expected in some conditions.

If We put the following expression :

Sum(If(CURRENCY=FromCur and DATE=FxDate,FX*PNL)

it works fine for all the currencies where CURRENCY<>ToCur.

If you now add the condition for ToCur;

Sum(If(CURRENCY=ToCur, PNL,

If(CURRENCY=FromCur and DATE=FxDate,FX*PNL))

It doesn't work as for the entries where CURRENCY=ToCur, you get multiple values.

There is a simple way to resolve this by adding a ToCur and FromCur value for same date with FX=1 for each currency.

Concatenate FXTable

Load Distinct FxDate,ToCur as FromCur, ToCur,1 as FX

Resident FXTable;

There might be other clever options to do this, but this one should work while keeping the expression simple.

[#4]

I'll try this :

=

if(CURRENCY=ToCur,

sum(PNL),

if(ACCOUNT='C1' or ACCOUNT='C2',

sum(if(CURRENCY=FromCur and xDate=DATE,YTDFX*PNL)),

sum

(if(CURRENCY=FromCur and FxDate=DATE,FX*PNL)))

)

This expression give me the good value but when i had partial sum, all value corresponding to CURRENCY=ToCur are ignored !!!

Regarding the number of values i can get (nb of day,...), i don't want to duplicate each currency ,e.g. USD USD 1.

[#5]

Hi,

There is a difference between Sum(If( condition, X,Y)) and If(Condition,Sum(X),Sum(Y)).

The soulution I suggested works fine, and it adds only one row for each day, any specific reason why you don't want to add that bit?

Will check what other options could we use.

[johnw]

I haven't read carefully, so apologies if I'm off base, but here's an example of currency conversion on the fly based on rates updated monthly.