How to compare Actual (monthly data) vs Budget (yearly data) in Qliksense by keeping monthly Actual numbers also

[a-5]

Hi! 

I'm new to Qliksense and have a data model in which I have one table (Budget) including budgets on customer, product and year level. In the second table (Sales) I have many different columns with sales data on a monthly level. Please see the attachment including a picture of how my data model looks like currently. In order to avoid circularity/synthetic keys I created a dummy field customer_product_year in Budget & Sales tables and that's used for linking these tables. 

I need to create a sheet, where I have graphs both about comparing Actual vs Budget on a product/customer/year level and also e.g. trend charts about sales - meaning I need to have actual sales numbers on a monthly level also.  Please see the other attachment about what type of graphs I'm looking for. 

Can someone please advise how to modify the data model to be able to create these charts?

Thank you!

-Anna

[Vilo]

It seems to me that the only different field you have in the budget table is the actual 'budget'. You could just join the budget to the sales table. Then you could still build your visuals on the front end using the dimensions you mentioned. 

[a-5]

Hi! Thank you for your comment!

Can you please assist how to do the join so that I don't get the budgets as duplicate/multiplex values? Because the budgets are defined on annual level and the actual results data is on monthly level.

Also we don't have a record in the actual monthly data for all products for all customers (if no sales for product x for customer y for example), but we have budget for product x and customer y. Just wondering how to get all the records from the budget table included in the final data table.