Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Canonical Calendar Challenges

I'm using the Canonical Calendar posted by Henric Cronstrom. (or let's say trying to use/implement)

But i'm facing some challenges.

I'm loading my sales data from a qvd.   and I have 3 dates i'm interested in. Which have their own calendar.   Invoice, order and Pick.

Sales:

LOAD  InvNumber,

           InvoiceDate,

           'Invoiced' as OrderStatus,

          InvMonth,

          InvYear,

          Company,

          OrderNumber,

          OrderType,

          OrderLineNumber,

          OrderDate,

          PickDate,

          Customer

          Sales Amount

(Company & '-' & OrderType & '-' & OrderNumber & '-' & OrderLineNumber) as OrderLineID

FROM

Sales.qvd] (qvd);

DateBridge:

Load OrderLineID, InvoiceDate as CalendarDate, 'Sales' as DateType

     Resident Sales;

   

Load OrderLineID, OrderDate as CalendarDate, 'Order' as DateType

     Resident Sales;

   

Load OrderLineID, PickDate as CalendarDate, 'Pick' as DateType

     Resident Sales;

This all Works fine.  Until i want to add my Budgets.

LOAD CalendarDate as InvoiceDate,

     Customer,

     'BU' As Forecast_Type,

     SalesBU as [Sales LCY BU],

     MarginBU as [Margin LCY LOC BU],

     Company,

   

FROM

[Budget.qvd]

(qvd);

Since in the ideal world the budget it perfect. It should always align with the invoicedate.. therefor there are no need for a forth calendar??

but when i load it.   I can't see budget numbers after the highest invoice date.  Even though i have forced in the calendar date til end of next year, in the calendar generator.   There are budget numbers until end of next year. But i can't see them.

Canonicial Calendar error.JPG

Temp:

LET varMinDate = Num(MakeDate(2013,1,1));

LET varMaxDate = Floor(MakeDate(2018,12,31));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

               $(varMinDate) + Iterno()-1 As Num,

               Date($(varMinDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

I'm suspecting it due to the DateBridge. Which are then bridging all the 4 calendars.  Main, order, invoice, pick.    this bridge is all done from the Sales table.  And it's somehow then limited to the highest dates from there.

What am i doing wrong?  Do i really need to create one more Calendar for the forecasts/budgets?  If so, how?  I don't have an Unique OrderlineID. since budgets are done on customers total. not on orderlines.  

If that is not the problem.  Then what is it?   I have also tried just loading it the budgets dates as CalendarDate. but this creates a loop.

15 Replies
hic
Former Employee
Former Employee

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

HIC

Anonymous
Not applicable
Author

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

hic
Former Employee
Former Employee

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

Anonymous
Not applicable
Author

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?

hic
Former Employee
Former Employee

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

Anonymous
Not applicable
Author

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