How To Convert Excel Formula into QlikView

[mohdhaniff]

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

[Digvijay_Singh · Accepted solution]

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

];

[mohdhaniff]

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.

[Digvijay_Singh]

Glad it worked for you