How to Approach This Problem - Sales vs Recommended Lists

[dmac1971]

I have 3 data sets, first data set last years parts sales.  2nd is historical list of machines and the last is a list of recommended sales for each machine.  I want to report for each account showing what they should purchase vs the recommended list, vs actual purchases, thus showing potential.

Easy to do in excel with a few cols, but not sure how to approach in Qlik.  The # machines are 50, and the list of recommended parts is over 8k, same parts can be repeated on multiple lists.  Machine sales span 17 years with matched account names.

I want to achieve a simple table like below, but not sure how to approach, possibly over thinking it?

[diegozecchini]

Hi!
I would load these datasets into Qlik and associate them properly. I would create a data model where:

The MachineList links Accounts to Machines.
The RecommendedParts list links Parts to Machines.
The LastYearSales table tracks actual purchases.

In Qlik, associations are made automatically if field names match. to sure that MachineID is present in both the Recommended Parts List and Machine List and PartNumber exists in both the Recommended Parts List and Last Year’s Sales.

Then, create measures in Qlik to calculate:

Actual Sales Qty (Sum of purchases): SUM(SalesQty)
Recommended Qty: SUM(RecommendedQty)
Shortfall (Recommended - Actual Purchases: SUM(RecommendedQty) - SUM(SalesQty)
Potential Revenue (If 50% of Shortfall is Purchased): (SUM(RecommendedQty) - SUM(SalesQty)) * SalePrice * 0.5

Now, create a pivot table or a straight table in Qlik Sense

Dimension 1: PartNumber
Dimension 2: Model
Measure 1: SUM(FleetSize)
Measure 2: SUM(FleetSize * RecommendedQty)
Measure 3: SUM(Actual Sales Qty)
Measure 4: % Rec Sold = SUM(SalesQty) / SUM(RecommendedQty)
Measure 5: SUM(Shortfall Qty)
Measure 6: SUM(SalePrice)
Measure 7: SUM(Potential Revenue)

Add filters for AccountName, Model, and Year.
Use a bar chart to show Shortfall Qty per Account.
Use a table to analyze Parts by % Recommended Sold.

[dmac1971]

Thanks for reply appreciated.  I do however have an account name as well.  Account name in spare parts table, and also in machines table.  So I want to be able to filter to an account, see their fleet, what they are recommended to purchase, vs what actually is purchased.