Sales VS Budget

[dino3989]

Hello experts.

im new to qlik sense, my task is to create sales vs Budget analysis, this is my data model for now.

we can assume that the budget remains the same for all years and its based on month and category.

thank you.

[simonaubert]

Hello,

You should better use a "concatenate" (qlik's union) between Budget and Sales and do a fact table with the two kinds of facts, with a fact type column  to identify the kind of fact.

something like that. Easy to understand, fast, accurate, accepts a lot of facts and a different level of aggregation.

[aravind_dussani]

Hi @Dino, It’s better to make 1Fact table for all dimensions as shown below.

Map_Fact_Category: Mapping load ProductID, CategoryID Resident Products;

Map_Fact_Supplier: Mapping load ProductID, SupplierID Resident Products;

DimensionBudget: Crosstable(Month,Budgetvalue) load CategoryID, Jan,Feb,etc,. Resident DimBudget;

Drop field Month from DimensionBudget; /***(please cross check the field values for Month in DimCalendar table n the values for Month in the above table are in same format n associated before dropping the Month field).*/ Map_Category_Budget: Mapping load CategoryID, BudgetValue Resident DimensionBudget;

MainFact:

Applymap(‘Map_Category_Budget’,CategoryID,’No CategoryID matched’) as BudgetValue;

Load *,

Applymap(‘Map_Fact_Category’,ProductID,’No ProductID matched’) as CategoryID, Applymap(‘Map_Fact_Supplier’,ProductID,’No ProductID matched’) as SupplierID Resident FactSales;

Drop field SupplierID,CategoryID from Products;

Drop tables FactSales,DimensionBudget, DimBudget;

Hope the above script helps..