Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Profit margin Help

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

6 Replies
MVP & Luminary
MVP & Luminary

Re: Profit margin Help

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Profit margin Help

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 

MVP & Luminary
MVP & Luminary

Re: Profit margin Help

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


talk is cheap, supply exceeds demand
Not applicable

Re: Profit margin Help

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
Valued Contributor III

Re: Profit margin Help

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

Not applicable

Re: Profit margin Help

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