topic Re: total in qlikview in QlikView

total in qlikview

Tue, 20 Oct 2015 15:45:28 GMT

Hello all,

Can you explain me how qlikview calculate the total in the field "Variation %" ?

PRIMARY_ASSET_CLASS	Overall Status	Number of Deals	Average Monthly Deals	Variation	Variation %
	4.0908	45,991	44,988	1,003	*4.0908*
InterestRate	0.0037	16,348	16,287	61	0.0037
ForeignExchange	0.0489	19,660	18,744	916	0.0489
Commodity	0.0030	5,207	5,191	16	0.0030
Equity	0.0065	3,267	3,246	21	0.0065
Credit	0.0071	1,509	1,520	-11	0.0071

The value 4.0908 is wrong the correct value is 0.02272 ----> 1003/44988
for information i calculate the field "Variation %"like this : =(fabs([Variation]))/[Average Monthly Deals]

fabs : absolute value

Thanks in advance ,

Regards,

Re: total in qlikview

hic — Tue, 20 Oct 2015 15:51:44 GMT

It depends on how your Variation and Average Monthly Deals are defined. QlikView does not just calculate it like Excel. Instead it uses the original formulas on the entire data scope.

HIC

PS Could it just be a simple typo? Your Variation % happens to be identical to your Overall Status.

Re: total in qlikview

vishsaggi — Tue, 20 Oct 2015 16:32:34 GMT

Is this what you are looking for??

PFA the sample report based on your data. But according to HIC it does not calculate like excel which is true.

However, let us know if this works out for you.

Thanks,
V.

Re: total in qlikview

Tue, 20 Oct 2015 16:43:06 GMT

Hello Henric,

Thanks for your answer. My variables are calculated like this :

Number of Deals = Count

( {1<[Application date]={$(vMaxDate)}, DealSent={1}>} Distinct RowNo_VIP_REP_EXT_TRADE_POS)

Average Monthly Deals =

Avg({1} aggr(Count( {1<[Application date]={">=$(vMinDate)<$(vMaxDate)"}, DealSent={1}>} Distinct RowNo_VIP_REP_EXT_TRADE_POS),PRIMARY_ASSET_CLASS,[Application date]))

Variation =

[Number of Deals]-[Average Monthly Deals]

Variation %

fabs([Variation]))/[Average Monthly Deals]

Thanks

Re: total in qlikview

Tue, 20 Oct 2015 17:04:44 GMT

Hello vishsaggi,

Your example works good for me. i have given the expression of variable that i have used.

Thanks