23 Replies Latest reply: Sep 9, 2011 5:26 AM by mrkachhia RSS

    GROSS PROFIT

       

      I have more than 100,000 lines showing invoice data. Few lines are shown below.

       

      Invoice NOPurchase AmountSold Amount
      INV001100125
      INV002125130
      INV003140148
      INV004150165
      INV005200250

       

      I want table as below

       

      Invoice NOPurchase AmountSold Amount% Profit
      INV00110012520.00%
      INV0021251303.85%
      INV0031401485.41%
      INV0041501659.09%
      INV00520025020.00%
      TOTAL71581812.59%

       

      Kindly help me.

        • GROSS PROFIT

          Use this:

           

          ([Sold amount]-[Purchase Amount])/[Sold Amount]

           

          Go to Number Tab:

           

          Select Fixed to: 2 Dec

           

          Tick on checkbox "Show in %age"

          • Re: GROSS PROFIT
            Anand Chouhan

            Hi,

             

            See the attached sample for clearity

            And suntotal of Purchase Amount, Sold Amount & % Profit.....

            By the help of straight table you get this in pivot it is not possible for the expression, there you do only on dimensions.

             

            Regards

            Anand

            • Re: GROSS PROFIT
              Anand Chouhan

              Hi,

               

              See the attached new updated file.

               

               

              Regards

              Anand

                • Re: GROSS PROFIT

                  Dear Anand,

                  Thanks for your quick response. But the total for % profit you shown is actually AVERAGE of % Profit. I need total of % profit which should be :  (818-715)/818 = 12.59%.

                    • Re: GROSS PROFIT

                      I am on personal so I can not see the sample data.

                       

                       

                      Try adding a dimension as ='Total'

                      Change the style of the pivot to indent mode.

                      make the expression for percent:

                       

                      if(left(Invoice,3)<>'INV', (sum([Sold amount])-sum([Purchase Amount]))/sum([Sold Amount]),

                      Sum(([Sold amount]-[Purchase Amount])/[Sold Amount]))

                       

                      This should tell it for the dimensions invoice to use the previous expression that worked, but if it is on the total of the new dimension total, to use the sum of each.

                       

                      Give that a try and see if it works.

                      • Re: GROSS PROFIT
                        Anand Chouhan

                        Hi,

                         

                        I got it see my next update file there is a litle trick but values are accurate i use this formula.

                        See the updated one.

                         

                        Reagrds,

                        Anand

                          • Re: GROSS PROFIT

                            Dear Anand,

                            Understood your trick but need right answer... LOL....

                              • GROSS PROFIT

                                is absolutely necessary to show the end of the column of profit? you can display on atable without dimension, only with expression

                                 

                                regards

                                • Re: GROSS PROFIT
                                  Anand Chouhan

                                  Hi,

                                   

                                  It is a trick by the way you can use like this.

                                   

                                  Regards

                                  Anand

                                    • Re: GROSS PROFIT
                                      John Witherspoon

                                      1 - sum(PurchaseAmount) / sum(SoldAmount)

                                        • Re: GROSS PROFIT

                                          Dear John,

                                           

                                          Thanks for your response. It's working perfectly.

                                           

                                          Just want to know if you can give me difference of below.

                                           

                                          1-PurchaseAmout/SoldAmount

                                          &

                                          1-sum(PurchaseAmount)/sum(SoldAmount)

                                           

                                          Thansk again....!!

                                            • Re: GROSS PROFIT
                                              John Witherspoon

                                              Do you understand SQL?  Just think of your chart like an SQL statement.  Your dimensions are your GROUP BY clause.  The total line is like an extra select unioned to the first.  Since you have sum of rows specified for the first two expressions, it's implicitly doing the sum().  So our chart with its summary line and the simpler expression is something like this:

                                               

                                              SELECT
                                              InvoiceNO
                                              ,PurchaseAmount
                                              ,SoldAmount
                                              ,1-PurchaseAmount/SoldAmount as Profit
                                              FROM INL14A
                                              GROUP BY InvoiceNO
                                              UNION ALL
                                              SELECT
                                              'Total' as InvoiceNO
                                              ,sum(PurchaseAmount) as PurchaseAmount
                                              ,sum(SoldAmount) as SoldAmount
                                              ,1-PurchaseAmount/SoldAmount
                                              FROM INL14A

                                               

                                              That's not legal SQL.  In the first select, you have a group by, so you MUST use an aggregation expression on fields that are not in the group by.  The second select is inconsistent, sometimes using a sum(), and sometimes not.  I suspect that isn't legal either.

                                               

                                              Now, QlikView doesn't strictly require an aggregation expression with a group by the way SQL does.  Or perhaps more accurately, there's an implied aggregation expression of only().  For clarity, I recommend EXPLICITLY stating the aggregation expression in all cases, even if it IS only().  I recommend pretending that QlikView is enforcing the SQL rule.

                                               

                                              Now we get to my solution.  All my solution said was to be consistent in the second "select".  You were summing for the first two columns, so you needed to sum for the third.  Since you couldn't use "sum of rows" to do this, you had to do it explicitly with sum() functions.

                                               

                                              But since I've already written all of this (I was trying to be brief before), I might as well tell you that the solution I gave you is NOT the solution I would personally use.  I would use "expression total" for all the total modes.  I would use the sum() explicitly in the PurchaseAmount and SoldAmount columns rather than using "sum of rows".  I would name the columns Purchase Amount and Sold Amount.  And then I'd calculate % Profit using the column names to avoid doing the sum()s a second time.  (Or at least I suspect that internally, QlikView would do them a second time.  It's possible it's clever enough to recognize that it's already done the calculations, but I'd rather just explicitly tell it it's already done the calculations by referring to the column names.)

                                               

                                              Dimension       = InvoiceNO
                                              Purchase Amount = sum(PurchaseAmount)

                                              Sold Amount     = sum(SoldAmount)
                                              % Profit        = 1 - [Purchase Amount]/[Sold Amount]