Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Date & Time | Symbol | Action | Qty | Price | Value |
9/12/2008 10:21 | A | Buy | 5 | 405 | 2025 |
9/12/2008 10:21 | A | Buy | 1 | 405 | 405 |
1/15/2010 10:06 | A | Buy | 420 | 118.8 | 49896 |
9/4/2009 10:11 | A | Buy | 30 | 121 | 3630 |
7/4/2011 15:17 | A | Sell | 189 | 90.4 | 17085.6 |
7/4/2011 15:17 | A | Sell | 11 | 90.4 | 994.4 |
8/3/2011 15:25 | A | Buy | 110 | 78 | 8580 |
11/18/2009 10:43 | B | Buy | 10 | 1000.85 | 10008.5 |
5/5/2010 9:53 | B | Sell | 10 | 1237.5 | 12375 |
4/29/2011 14:15 | B | Buy | 20 | 1280 | 25600 |
7/25/2011 14:05 | B | Sell | 15 | 1334.15 | 20012.25 |
6/16/2011 9:59 | B | Buy | 7 | 1210 | 8470 |
7/13/2011 14:11 | B | Buy | 1 | 1267.9 | 1267.9 |
7/13/2011 14:11 | B | Buy | 14 | 1267.9 | 17750.6 |
8/11/2011 14:37 | B | Buy | 5 | 1220 | 6100 |
8/11/2011 13:40 | B | Buy | 5 | 1230 | 6150 |
7/1/2011 14:09 | B | Sell | 27 | 1310 | 35370 |
above it he Input file And I want to display as report as below
Symbol | Qty | Avg | Total Amt | Realized Profit |
A | 366 | 109.27 | 39992.8 | -6462.11 |
B | 10 | 1225 | 12250 | 4660.25 |
Realized profit is what I am intereseted. Once person sells in all the preious buy(avg of previous) subtraction of current sell gives realsied profit.
But new buy should not change the Realized profit until there is subsequent sell.
Please an body would help me to do as above.
hi,
Can you describe your problem with expression used and with a example.
HTH
Hi There,
There are a few ways how you can solve this.
I would start to add the following line to your loading script:
if(Action = 'Buy', Qty, (Qty)*-1) as CallQty,
This turns your quantity with action Sell in to a negative number.
So SUM(CallQty) gives you Buy-Sell.
Let me know if this works for you.
Good luck,
Dennis.
I am intereseted in the finding the Realised profit. That depends upon the Sell of the product i.e. till it reads in sell. all the avg buy(prior to current sell not after the sell row(i.e new buy not added unitl there is sell and avg buy has changed due to this) minus the current sell. That is where i am facing the problem.
For ex. product A till 4th row is as follows( mentally calculation as follows but how to do in Qlikview?)
Qty Total amount Profit Avg
456 | 55956 | 0 | 122.71 |
When the 5 the row is read in
it would like that
Qty Total amount Profit Avg
256 | 37876 | -6462 | 122.71 |
and when the 6th row is read it should be as displayed in the output.
Symbol | Qty | Avg | Total Amt | Realized Profit |
A | 366 | 109.27 | 39992.8 | -6462.11 |
I Have presented just one Product. Similary for the other product.
Hope it clarifies what I am looking for.
@Dennis thanks for the reply.
But it wont find Realized profit as the that would be cummalitive of all the buy minus the sell. But I am looking for realised profit which depends upon current sell minus all previous buy which will give me my profit. And that subseqent buy is add that does not alter my realised profit(which is already booked) until i sell the remaining.
Above i presented the exact calculation which is to be done but how to do in Qlikview not sure about it.
Trd.Date & Time | Symbol | Action | Trd.Qty | Trd.Price | Trd.Value |
9/12/2008 10:21 | A | Buy | 1 | 405 | 405 |
9/12/2008 10:21 | A | Buy | 5 | 405 | 2025 |
9/4/2009 10:11 | A | Buy | 30 | 121 | 3630 |
1/15/2010 10:06 | A | Buy | 420 | 118.8 | 49896 |
7/4/2011 15:17 | A | Sell | 11 | 90.4 | 994.4 |
7/4/2011 15:17 | A | Sell | 189 | 90.4 | 17085.6 |
8/3/2011 15:25 | A | Buy | 110 | 78 | 8580 |
11/18/2009 10:43 | B | Buy | 10 | 1000.85 | 10008.5 |
5/5/2010 9:53 | B | Sell | 10 | 1237.5 | 12375 |
4/29/2011 14:15 | B | Buy | 20 | 1280 | 25600 |
6/16/2011 9:59 | B | Buy | 7 | 1210 | 8470 |
7/1/2011 14:09 | B | Sell | 27 | 1310 | 35370 |
7/13/2011 14:11 | B | Buy | 1 | 1267.9 | 1267.9 |
7/13/2011 14:11 | B | Buy | 14 | 1267.9 | 17750.6 |
7/25/2011 14:05 | B | Sell | 15 | 1334.15 | 20012.25 |
8/11/2011 13:40 | B | Buy | 5 | 1230 | 6150 |
8/11/2011 14:37 | B | Buy | 5 | 1220 | 6100 |
Input file properly sorted as per the transcation in temily manner for calulation.
Output Should be
Symbol | Qty | Avg | Total Amt | Realized Profit |
A | 366 | 109.27 | 39992.8 | -6462.11 |
B | 10 | 1225 | 12250 | 4660.25 |