topic Re: How To Convert Excel Formula into QlikView in QlikView

How To Convert Excel Formula into QlikView

mohdhaniff — Wed, 25 Nov 2020 16:16:04 GMT

I am creating below formula in Excel (refer to Net Calculator):

Amount	Rate	Duration	Charges	Rate Charge	Net
58400	3.50%	108	18396	3.5	6.344015796

Can anybody assisi me on how to convert 'Net' Field into QlikView formula?

My SQL as below:

data:
LOAD * Inline [
Amount,Rate,Duration
58400,3.50%,108
];

Count:
NoConcatenate
LOAD *,
Amount*Rate*(Duration/12) as Charges
Resident data;
Left Join (Count)
LOAD *,
(Charges/Amount)/(Duration/12)*100 as [Rate Charge] Resident Count;
LOAD *,
SUM(2*(Duration*[Rate Charge])*(300*12+Duration*[Rate Charge]))/((2*(Duration)^ 2 * [Rate Charge])+(300 * 12 * (Duration + 1))) as Net;

DROP Tables data;

Thanks

Re: How To Convert Excel Formula into QlikView

Digvijay_Singh — Wed, 20 Jul 2016 08:20:27 GMT

Try this, don't understand the use of sum, at least this gives the result for the sample, -

Data:

Load *,

2*(Duration*[Rate Charge])*(300*12+Duration*[Rate Charge])/((2*(Duration*Duration)*[Rate Charge])+(300*12*(Duration+1))) as Net;

LOAD *,

(Charges/Amount)/(Duration/12)*100 as [Rate Charge];

LOAD *,

Amount*Rate*(Duration/12) as Charges;

LOAD * Inline [

Amount,Rate,Duration

58400,3.50%,108

];

Re: How To Convert Excel Formula into QlikView

mohdhaniff — Thu, 21 Jul 2016 01:01:36 GMT

Hi Digvijay Singh

Actually I got the formula from my senior and also wondering what is purpose of SUM in the excel. I already try to convert the formula since past 3 weeks.

Anyway, thank you.

Re: How To Convert Excel Formula into QlikView

Digvijay_Singh — Thu, 21 Jul 2016 07:14:56 GMT

Glad it worked for you