Profit margin Help

[#1]

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

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

[#3]

so far this is what i have done.

Step 1

add data

Ebay Sheet

Paypal Sheet

Shipping Sheet

Data Base Sheet

Step 2

create assassination between

Ebay sheet and PayPal sheet  Transaction ID

Ebay Sheet and Paypal Sheet Transaction ID

Ebay Sheet and Shipping Sheet Transaction ID

Ebay Sheet and DataBase SKU

Step 3

Load Data

Please tell if so far all is good.

Next i need to find matching information and run the formula to calculate the sold price minus fees and charges from he cost. How can i achieve that section 

[Gysbert_Wassenaar]

Please tell if so far all is good.

I'm afraid I have no idea if all is good. I can't see what you've done and what the data model in your Qlikview document now looks like. Perhaps you can create a small qlikview document with some example data. See this document: Preparing examples for Upload - Reduction and Data Scrambling

[#5]

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.

[rahulpawarb]

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

[#7]

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