How to generate report as follows based on provide... - Qlik Community

chetan_surana · ‎2011-09-26

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.

sushil353 · ‎2011-09-26

hi,

Can you describe your problem with expression used and with a example.

HTH

Anonymous · ‎2011-09-26

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.

chetan_surana · ‎2011-09-26

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.

chetan_surana · ‎2011-09-26

@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.

chetan_surana · ‎2011-09-26

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

How to generate report as follows based on provided input