Partial sums, unexpectd result

Tue, 25 Mar 2014 10:51:00 GMT

Hi All,

I'm trying to add up a set of monetary figures in a pivot table. The income comes in in several different currencys. Depending on what currency the product is bought in, It'l then convert it to sterling pounds. From this point, im trying to add up the new sterling amounts

There are a number of sliders which can alter the variables for the currency conversions which the user can set within the app. So it can be altered to the changing currency rates, so this isn't done via script.

The pivot table itself is quite simple with dimension of

MemberType

MbrSub_Price

month

year

the expression is as follows:-

if(MbrSub_CurrencyId = 1 , sum((MbrSub_Price / $(HKDollar))/MbrSub_Duration) ,

if(MbrSub_CurrencyId = 2 , sum((MbrSub_Price / $(USDollar))/MbrSub_Duration),

if(MbrSub_CurrencyId = 3 , sum((MbrSub_Price / $(NNaire))/MbrSub_Duration) ,

if(MbrSub_CurrencyId = 4 , sum((MbrSub_Price / $(CYuan))/MbrSub_Duration) ,

if(MbrSub_CurrencyId = 5 , sum((MbrSub_Price / $(NZDollar))/MbrSub_Duration),

if(MbrSub_CurrencyId = 7 , sum((MbrSub_Price / $(Euro))/MbrSub_Duration) ,

if(MbrSub_CurrencyId = 8 , sum((MbrSub_Price / $(IRupee))/MbrSub_Duration),

sum( MbrSub_Price/ MbrSub_Duration))))))))

Each line that is produced is working fine and calculating correctly ,

totals are calculated by using partial sums on the product - but these are incorrect

I have a list box with all the different currencys in it, if one of these are selected the totals work fine however, if i deselect or select 2 currencys then the partial sums total is equal to the original calc totals without any conversion

i.e.

sum( MbrSub_Price/ MbrSub_Duration)

due to the nature of the sheet, im sorry but i cant add the actual file but ive done a mock up

Basically I just want it to add up whatever it sees in the column

so if you look at the 2 columns original and converted , original calculates correctly but converted doesn't

any ideas ?

thank you

Re: Partial sums, unexpectd result

lironbaram — Tue, 25 Mar 2014 11:26:38 GMT

hi attach is an example

you need to use aggr function , that creates an array of values for each row and summarize them

your function doesn't work because in the total row you have all currencies

Re: Partial sums, unexpectd result

Tue, 25 Mar 2014 11:52:54 GMT

Thank you

topic Partial sums, unexpectd result in QlikView

Partial sums, unexpectd result

Re: Partial sums, unexpectd result

Re: Partial sums, unexpectd result