Calculation

[gnmq]

Hi

I need an urgent help on how to compute the following:

NewColumn = (Today's data - Ytd's data ) / Ytd's data

It is a financial data, I would like to derive the above column based on trade date and amount field..

Sample data:

Trade_date, FundCode, Amount, NewColumn

12-Dec-2019, A, 500

13-Dec-2019, A, 300

For 13 Dec 2019, the new column value will be (300 - 500)/500

=> Condition on FundCode for calculation as well.

May I know how I can implement this on QS?

[ashishkalia]

Hi @gnmq ,

It would be easier to help if you provide some more context to your requirements/Issue.

Regards,
AK

[gnmq]

Hi AK,

I have updated my question. Thanks for the reply

[ashishkalia · Accepted solution]

Hi @gnmq ,

Based on your sharing:

"12-Dec-2019, A, 500
13-Dec-2019, A, 300
For 13 Dec 2019, the new column value will be (300 - 500)/500"

Above can be achieved in backend by creating a new field as below:
Load Trade_date,
           FundCode,
           Amount,
          if(FundCode = Previous(FundCode),
           (Amount - Previous(Amount))/Previous(Amount),  Amount)       as     NewColumn
From XYZ<File Path  & Name>
Order By Trade_date, FundCode;

Let me know if it works.

Regards,
AK

[gnmq]

Hi AK,

This works for me. Thanks so much for your help!