Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
QlikSense...
I am struggling with Multiple Facts in the traditional Sales/Orders/Budget type model.
I have my tables of Orders & Sales with psuedo-key of Day, Product, Currency, Country & Customer (Key_Fact_Sales)
I have my Budget table, which is month, with pseudo key of Month, Product, Currency & COuntry. (Key_Fact_Budget)
These are concatenated into a link table.
I have separate dimension tables for Time (Time has Day, Month, Year etc), Product, Customer, Country & Currency.
Sales + Orders works fine.
However, when I add in Budget to the table, no data is returned - I think this is because of the different Key in the link table.
How best to resolve this?
I have thought of adding Key_Fact_Budget keys to the Sales/Orders tables, but this would imply that when I look at YTD performance vs annual budget, the budget calcs would only consider those periods where there are corresponding Sales.
Any suggestions
Have you follow the guidelines outlined here: Concatenate vs Link Table
You say that you concatenate to create a link table. That doesn't sound right to me. A CONCATENATE LOAD would not create the link table you need.
There is an open source project called QlikView Components which to a large degree can be used in Qlik Sense also. Among the functions covered in this library is one that create a link table for you:
GitHub - RobWunderlich/Qlikview-Components: A library for common Qlikview Scripting tasks
Thanks Petter,
I have concatenated my pseudo keys into a link table, using the approach outlined... changing it to not concatenate took me a step forwards, in that current month budget now comes through...
However, i still seem to be unable to get budget numbers for months where there are no orders or sales.
You could populate your Sales and Orders tables with rows containing null values or 0's in the metrics fields for these months.
The solution I found for this issue was to have a Sales & Orders Fact table with a link table to the dimensions.
The Budget Fact connected directly to the dimensions due to the different granularity of the data (month vs day)