Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Re: Canonical Calendar Challenges

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.

Data Model.png

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?

Budget table metadata.png


New Contributor

Re: Canonical Calendar Challenges

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??

Re: Canonical Calendar Challenges

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

View solution in original post

New Contributor

Re: Canonical Calendar Challenges

But I can't do both??  Should i then do an IF statement?   IF Len(Invoicedate) = 0  then one kind of orderID.  Else the other?

Re: Canonical Calendar Challenges

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...

New Contributor

Re: Canonical Calendar Challenges

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..