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.

1 Solution

Accepted Solutions
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

View solution in original post

15 Replies
sunny_talwar

I think the problem is that there is no (Company & '-' & OrderType & '-' & OrderNumber & '-' & OrderLineNumber) as OrderLineID for the budget data.... since that doesn't align, Invoice Date is not really connected to Date and calendar.... Do you have budget at Company, OrderType, OrderNumber, OrderLineNumber level? I would think not, right?

Anonymous
Not applicable
Author

No i don't..

So should i change my Date bridge to be more "loose".. so it's not bridging one each single line in the sales table?  Or how would i fix this? 

The Dates i need to focus on, can be different on two orderlines, within the same order..  But you never budget on orderlines.. Just as a total. Where we load it with a single date in that month.  so the budgetted sales for a month is also on the 28th of that month.

sunny_talwar

I think you might need to try one of these

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;

Concatenate (DateBridge)

LOAD Company,

    CalendarDate,

    'Budget' as DateType

FROM [Budget.qvd] (qvd);

or

Concatenate (DateBridge)

LOAD Company as OrderLineID,

    CalendarDate,

    'Budget' as DateType

FROM [Budget.qvd] (qvd);

My link table skills are not the best these days (quite a bit out of touch), but see if that helps... Make sure to check it with and without selections (specially with selections in things like Order and OrderNumber and OrderLineNumber)

Anonymous
Not applicable
Author

hmm.. this turned my load script from a 2 min. load to a 12 min. load. Which then also failed. :-(..  both of them  but i can see the logic your are trying to use.. maybe it's a small detail which is wrong in it

sunny_talwar

Would you be able to share the script you just tried?

Anonymous
Not applicable
Author

So.. here's a small sample with scrampled data..

I had forgot the  paste the code yesterday.. so only the first example prolonged the load time.. the second just dosn't seem to work..

!

Anonymous
Not applicable
Author

So I tried Creating a "fake"  OrderlineID in the Budget load. and Naming the date something else.

LOAD CalendarDate as ForecastDate,

    SellToCustomer,

    'BU' As Forecast_Type,

    SalesBU as [Sales LCY BU],

    MarginBU as [Margin LCY LOC BU],

    Company,

    SalesBU / ApplyMap('Rates', Company, 0) as [Sales GBP BU],

    MarginBU / ApplyMap('Rates', Company, 0) as [Margin GBP LOC BU],

    (Company & '-0-0-1') as OrderLineID

FROM

(qvd);

And then add it to the datebridge

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;

Load OrderLineID, ForecastDate as CalendarDate, 'Budget' as DateType

    Resident BU_FORECAST;



And now i just get the Total in ALL months??  The total is correct. So i'm thinking i'm not entirely off?

Anonymous
Not applicable
Author

Will just try to Bump this.. I'm thinking someone must be able to help. It's seems like it's an easy fix, where i'm missing a little piece of the puzzle..

Anonymous
Not applicable
Author

Okay.. so it's seems there are no one to help on this issue.. are there places out there, where we could buy the assistance then?  preferable online. since i'm guessing it's a quick fix