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.

[Anil_Babu_Samineni]

Perhaps this? for below comment, And they try from your end, as i didn't follow why 15 multiply with last unit order rather 16 ??

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)

If((SUM({<type={'Sale'}>} Units)-sum({<type={'Purchase'}>} Units))> Sum({<Date = {'$(=Min(Date))'}>} Units),

(SUM({<type={'Sale'}>} Units)-sum({<type={'Purchase'}>} Units)) - Sum({<Date = {'$(=Min(Date))'}>} Units))

[swatitomar]

Why multiply by 15 not 16 because we want to check the Value on the basis of NAV Purchase Date not Sale .

[Anil_Babu_Samineni]

If you looks the image, Even i would think 16 because of highlighted in image. Can you brief on this, please.

[swatitomar]

This totally Based on ID .

For ID

3.    (16*40)=640

2.  (15*400) + (10*200) = 8000

1.  ( 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.

We are Calculating Value=NAV*Units

and logic is diff. in case of ID has both Sale/Purchase otherwise

its very simple in case of only Purchase

[Anil_Babu_Samineni]

What are you getting when you use this? I am assuming, Your date format is

SET DateFormat='MM/DD/YYYY';

And Expression like below

(Sum({<type = {'purchase'}, Date = {'$(=Min(Date))'}>}NAV * Units)) + (Sum({<type = {'purchase'}>} NAV * Units))

[swatitomar]

Yes Date Format is right.

You are getting the same answer by applying your that i have mentioned for ID 1 =3110?

[Anil_Babu_Samineni]

That you need to tell, Because i haven't tested that. Not really sure why 10*11 again? Here are you going to calculate only for NAV which is for purchase? If so, You may require to use If..Else statement. May be explain this part bit clear

[swatitomar]

Hi Anil,

1. Output was not correct when I applied your logic.

2. Yes, I like to cal. the Investment Amount ..its formula is (Purchase Units *NAV of purchase Units)

3. Will explain you the logic again: In Logic i'll use the term Bal i.e.  (sum(Purchase)-Sum(Sale)) i.e. 210

4. Work against ID

If (ID has Sale and Purchase both ,

if  (Bal -Last Purchase Unit )<=0, last Purchase Units *NAV of last purchase Units,

Bal -Last Purchase Unit  i.e.

210  -  200 =   10  (10 is the remaining bal. )     (200*15=3000 ->Last Purchase Units *NAV of Last purchase Units)

so, now we check  the remaining bal. 10 with above of last purchase unit     (10*11=110 ->Above of Last Purchase Units *NAV of above Last purchase Units),

sum(Purchase Units *NAV ))

you can check the below Result in table  and in text box 11750 sum of all fund

ID	Value
1	3110
2	8000
3	640

[Anil_Babu_Samineni]

I believe you are going to having this?

Sum({<Date = {'$(=Date(Max(Date)))'}, type = {'purchase'}>} (NAV*Units))+

(Sum({<Date = {'$(=Date(Min(Date)))'}, type = {'purchase'}>} NAV)*Sum({<Date = {'$(=Date(Max(Date-1)))'}, type = {'purchase'}>} NAV))

If not stalwar1‌ can care of this