6 Replies Latest reply: Jan 13, 2017 4:24 PM by ohannis dikramanjian RSS

    Profit margin Help

    ohannis dikramanjian

      Hello Guys,

      I am trying to create a App that will analyse my ebay sales. The challenge is that i dont have all the data in one place. I have the following excel exports.

      Ebay: Sales history which includes transaction ID, Sold Price, Ebay Fees, Customer Information, SKU

      Shipping software: transaction ID, Customer information, shipping Fees

      Paypal: Transaction ID, Paypal Fees, Customer information

      Our Database dump excel: product cost, SKU.

       

      I am trying to achieve the following.

      Link Ebay sheet to Shipping sheet and Paypal sheet  using the transaction ID, this will find and match all the identical transactions, also I want to link our data base shee to Ebay sheet using the SKU, Final step will be as follow

       

      Transaction ID, SKU, Sold Price - Ebay Fee - Paypal Fee - Shipping Fee  then take the total and deduct it from the cost.

       

      my main question is can we achieve this in qlik ? If yes can i make the application so that i can import these sheets on daily bases to create daily profit reports ? and HOW can i make this happen, I have tried to play around the application, watched the videos but it is over whelming.

      Any help will be highly appreciated.

       

      Thanks

        • Re: Profit margin Help
          Gysbert Wassenaar

          That shouldn't be a big problem.

           

          The ebay sales can be loaded into a Sales table. The transaction ID field is the logical choice for the key field that will link it to the Shipments table and the Payments table.

           

          Load the shipments data in a Shipments table that's linked with the Sales and Payments table using the transaction ID key.

           

          Load the paypal payments data in a Payments table that's linked with the Sales and Shipments table using the transaction ID key.


          All the customer information can be consolidated in a Customers table. Keep only a Customer ID key in the Sales table to link it with the Customers table.

           

          Finally the Products table with the data from your database which is linked to your Sales table using the SKU ID field.

           

          Make sure the key fields that are used to link the tables have the exact same case sensitive name in the tables that should be linked.

          • Re: Profit margin Help
            ohannis dikramanjian

            I am using quick sense t does not have save option. Do you want me to drop it in a dropbox and share it ?

            by the way thanks for all your help.

            • Re: Profit margin Help
              Rahul Pawar

              Hello Ohannis,

               

              Hope you are doing well!

               

              Please use below sample script which will join & load data from different sources and calculate the required field. You can modify the script as per your need.

               

              SampleSales:
              LOAD TransactionId, 
                   SoldPrice, 
                   eBayFees, 
                   CustomerInformation AS eBayCustomerInformation, 
                   SKU
              FROM
              SampleData.xlsx
              (ooxml, embedded labels, table is eBay);
              INNER JOIN
              LOAD TransactionId, 
                   CustomerInformation AS ShippingCustomerInformation, 
                   ShippingFees
              FROM
              SampleData.xlsx
              (ooxml, embedded labels, table is Shipping);
              INNER JOIN
              LOAD TransactionId, 
                   CustomerInformation AS PaypalCustomerInformation, 
                   PaypalFees
              FROM
              SampleData.xlsx
              (ooxml, embedded labels, table is Paypal);
              INNER JOIN
              LOAD ProductCost, 
                   SKU
              FROM
              SampleData.xlsx
              (ooxml, embedded labels, table is DBDump);
              
              Sales:
              LOAD TransactionId,
                   SKU, 
                   SoldPrice, 
                   eBayFees,
                   PaypalFees,
                   ShippingFees,
                   ProductCost,
                   ProductCost - (SoldPrice - eBayFees - PaypalFees - ShippingFees) AS Profit
              Resident SampleSales;
              
              DROP Table SampleSales;
              

               

              Hope this will help. !

              Regards!

              Rahul

              • Re: Profit margin Help
                ohannis dikramanjian

                I'm attaching the file, can you please modify so that i can make more sense I really appreciate your help.