5 Replies Latest reply: Sep 26, 2011 6:47 AM by Chetan Surana RSS

    How to generate report as follows based on provided input

    Chetan Surana
      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


      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.

        • How to generate report as follows based on provided input
          sushil kumar



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




            • How to generate report as follows based on provided input
              Chetan Surana

              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


              When the 5 the row is read in

                it would  like that

                      Qty                  Total amount  Profit  Avg



              and when the 6th row is read it should be as displayed in the output.

              SymbolQtyAvgTotal AmtRealized Profit


              I Have presented just one Product. Similary for the other product.

              Hope it clarifies what I am looking for.

            • How to generate report as follows based on provided input
              Dennis Hoogenboom

              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,


                • How to generate report as follows based on provided input
                  Chetan Surana

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

                    • How to generate report as follows based on provided input
                      Chetan Surana
                      Trd.Date  & TimeSymbolActionTrd.QtyTrd.PriceTrd.Value
                      9/12/2008 10:21ABuy1405405
                      9/12/2008 10:21ABuy54052025
                      9/4/2009 10:11ABuy301213630
                      1/15/2010 10:06ABuy420118.849896
                      7/4/2011  15:17ASell1190.4994.4
                      7/4/2011  15:17ASell18990.417085.6
                      8/3/2011  15:25ABuy110788580
                      11/18/2009 10:43BBuy101000.8510008.5
                      5/5/2010 9:53BSell101237.512375
                      4/29/2011  14:15BBuy20128025600
                      6/16/2011  9:59BBuy712108470
                      7/1/2011  14:09BSell27131035370
                      7/13/2011  14:11BBuy11267.91267.9
                      7/13/2011  14:11BBuy141267.917750.6
                      7/25/2011  14:05BSell151334.1520012.25
                      8/11/2011  13:40BBuy512306150
                      8/11/2011  14:37BBuy512206100



                      Input file properly sorted as per the transcation in temily manner for calulation.

                      Output Should be

                      SymbolQtyAvgTotal AmtRealized Profit