The problem lies in the data model.
As you can see in the picture below, your Canonical date links to the Sales table, which in turn links to the Budget table. In other words - dates that exist in the Budget table but not in the Sales table will not link to your Canonical calendar.
I would probably change the data model: I would concatenate the Sales and the Budget tables (keep the names of the fields InvoiceDate, SellToCustomer and Company) so that your date bridge links to both sales and budget records. This would also remove the synthetic table, which might be part of the problem. If you concatenate the two before you crate the date bridge, I suspect everything will work just fine.
But you have an additional problem: Your budget table seems to contain too many records - twice as many as the sales table. If I look into it, it seems as if there are duplicates. Could this be the case?
Thanks for the reply..
I will start with the last question. I don't think so. This sample was highly reduced. so i was able to upload it. So it contains 6 months of sales. or so.. But the budget contains 12 months of budgets for all existing customers. so all customers with a minimum of 12 date per customer. That is a lot of records. so on the top off my mind i don't think it's unnatural that it's bigger in this sample.
reading your response made me think about how the Canonical calendar Works. And if i understand it correct. it's basicly telling qlikview which orderID's to sum on. given a selected canonical date right? So my second attempt was failing since i added a "static" OrderId for the forecast(i got the full budget in all months)
Company & '0-0-0' as orderid. So all lines had the same OrderID.. so when selecting a calendardate. It would show the total. Since it was selecting the "unique" order ID, which were on Every single record.
But if i create a Unique OrderID for all records. then now it's working. The OrderID dosn't need to match the orderID's in the sales table correct? I just need a field called OrderID. so the canonical calender call tell qlikview the "unique" sum criteria.. Right?
Well now it's working.. But from your response, I'm now thinking if this is the wrong way to do it. If i could avoid the Syn key.
I have 6 forecasts(just 1 in the sample).. Can i simply concatenate them all into the sales table? Would this be a better praxis? And i thought i need the same fields in the to table which are to be concatenated? Also since i don't have alot of the fields which i need to create a unique sales ORDERID, in my budgets. since i budget on a customer level.. but the sales are down to orderline level.. how will i then create a UNIQUE key for all the lines in the new concanated sales table?
I have attached a new sample. where it's now "working". but maybe not in the Correct way??
I think it would be better to concatenate all forecasts with the sales table into one big fact table. And no, you don't need to have all fields identical to concatenate. The Concatenation will match fields that are named the same and pad the others with NULLs.
If you want a REALLY unique OrderLineID, you could use
RowNo() as OrderLineID
but I think that what you are doing now is sufficient. It has a grain fine enough to pick out the right records:
Company & '-' & OrderType & '-' & OrderNumber & '-' & OrderLineNumber as OrderLineID
Company & '-' & CalendarDate &'-' & SellToCustomer &'-1' as OrderLineID
I think you can use the definitions of OrderLineID that you have. And both the Sales table and the Budget table have well defined InvoiceDates, so I don't see why you would need an If() function...
Sorry i don't understand.
if you don't use both kind of ORDERID'S how does it then create unique ID's?
in sales right now. on of the "crucial" fields for creating a unique key are the Orderline number.
in the forecasts' it's the selltocustomer and date.
So if i understand you correct. i will simply add more fields to the string. so it would be
company & sellto & date & order & orderline as orderID.
Thanks for the replies..