Logic of Last in First out

[swatitomar]

Hi Team,

i'm working on logic Last in First Out  logic  to calculate the Value =NAV * Unit but stuck in between please help me out in getting the result.

I have the below data

ID	type	NAV	Units	Date
1	purchase	10	100	10/9/2012
1	purchase	12	200	11/1/2012
1	purchase	11	150	11/8/2012
1	Sale	15	400	12/2/2012
1	purchase	15	200	12/3/2012
1	Sale	16	40	12/8/2012
2	purchase	15	400	12/2/2012
2	purchase	10	200	12/3/2012
3	purchase	16	40	12/8/2012

and I want Value=Unit * NAV but logic is different in case fund has both type i.e.  Sale and Purchase.

if field ID has both (Purchase/ Sale) type den SUM({<type={'Sale'}>} Unit)-sum(type={Purchase}>} Unit)=Balance Unit i.e 210 in case OF ID=1 .

Now I want to work with Balance Unit 210 for ID =1 .

210 should get minus from last purchase unit  and if( Balance Unit  > last purchase unit  (210>200)) den will go above of Last Purchase unit with remaining balance i.e. 10 (210-200)

( 200*15=3000) Last Purchase Unit* NAV of Last Purchase

+

( 10*11)=110  Above of Last Purchase Remaining Unit* NAV of Above of  Last Purchase

total of ID=1 is 3110

Result is this in Chart and in text box 11750 sum of all fund

ID	Value
1	3110
2	8000
3	640

Thanks In Adavnce.

[sunny_talwar]

May be this

=Sum(Aggr(If(Sum({<type={'Purchase'}>} Units) - Sum({<type={'Sale'}>} Units) > FirstSortedValue({<type = {'purchase'}>} Units, -Date),

FirstSortedValue({<type = {'purchase'}>} Units*NAV, -Date) +

(Sum({<type={'Purchase'}>} Units) - Sum({<type={'Sale'}>} Units) - FirstSortedValue({<type = {'purchase'}>} Units, -Date)) * FirstSortedValue({<type = {'purchase'}>} NAV, -Date, 2),

Sum({<type={'Purchase'}>} Units*NAV)), ID))

Although, I was not sure about when Balance Unit  > last purchase unit was not true... I just used this Sum({<type={'Purchase'}>} Units*NAV)), ID)... but it could be wrong as you didn't mention what needs to be done when the condition fails. (ID = 3 Fails the condition, I guess)

[Anil_Babu_Samineni]

But, You star and finally we will get output

[swatitomar]

hi Sunny,

Thank u so much for your reply.

Output is correct , but it will not fully correctly if I change the data.

I think we should do it in script with the help of peek().

In the logic, the main concern is to run the loop till Bal. (sum(Purchase)-Sum(Sale)) become zero and that Bal. will check with purchase Unit from Last- Top Purchase Unit

  2nd Case:  

ID	type	NAV	Units	Date
1	purchase	10	100	10/9/2012
1	purchase	12	200	11/1/2012
1	purchase	11	150	11/8/2012
1	Sale	15	400	12/2/2012
2	purchase	15	400	12/2/2012
2	purchase	15	200	12/3/2012
3	purchase	16	40	12/8/2012

Exp. shared by you will incorrect ans. in this case  For ID 1 output is  550  and Bal.  450-400 = 50(Bal.)<last purchase Unit so 50*11 550

3rd  Case: For ID 1: Bal. 190 (650-460 ). output of this 4530  

ID	type	NAV	Units	Date
1	purchase	10	300	10/9/2012
1	purchase	12	200	11/1/2012
1	purchase	11	50	11/8/2012
1	Sale	15	400	12/2/2012
1	purchase	15	100	12/3/2012
1	Sale	16	40	12/8/2012
1	Sale	17	20	20/8/2012
2	purchase	15	400	12/2/2012
2	purchase	15	200	12/3/2012
3	purchase	16	40	12/8/2012

Explanation of above 3rd Case: As you can see in below chart in Rem. Unit field , I check the Bal till 0. from last to top purchase Unit.

Bal i.e. 190 check Bal. with Units in Rem. Unit

ID	Date	type	NAV	Units	Rem. Unit	last-top	Value
1	10/9/2012	purchase	10	300
1	11/1/2012	purchase	12	200	(90-50) =40<200	3	40*12=480
1	11/8/2012	purchase	11	50	(190-100)=90>50	2	50*11=550
1	12/3/2012	purchase	15	100	190>100	1	100*15=1500

I'm main focusing on ID 1 as it has both Sale and Purchase and in other cases it has purchase only so it is showing correct value

[sunny_talwar · Accepted solution]

Check attached and see if this resolves your issues

[swatitomar]

thank u so much both of you..

I made little changes in script..