Finding previous Year's Amount using a field in Single Fact table

[prathipsrinivas]

Hi Qlik Experts,

I have a single fact table, where I have fields like 

Policy,

RenewedfromPolicy,

Amount.

Here, If  RenewedfromPolicy(Old Policy) which is last year Policy Number,  from which Policy(new Policy) is derived.

I want to show in a  single straight table, Amount for new policy and Old policy something like below.

Policy, RenewedfromPolicy, Amount, PreviousPolicyAmount.

I have tried something like:-

Current:

load

Policy,

RenewedfromPolicy,

Amount 

from table;

left join (Current)

load 

RenewedfromPolicy,

sum(Amount) as PreviousPolicyAmount

resident Current

group by RenewedfromPolicy;

somehow it does not work out.

Could you please help out how to achieve this.

Thanks in advance,

Prathip

[prathipsrinivas]

Hi Guys,

Just wanted to know, is it possible to handle it in the Straight table itself rather than writing this logic in the Script?

Something like this:-

RiskReference, RenewedRiskReference, Amount, Previous Amount

123, 023, 23456,78654

backend data is like this:-

RiskReference, RenewedRiskReference, Amount

123,023,23456

023,-,78654

It would be really useful if I get any suggestions here.

Thanks,

Prathip

[sunny_talwar]

Would you be able to provide a sample or some sample data to help you better here?