Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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 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?
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.
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)
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
Would you be able to share the script you just tried?
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..
!
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?
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..
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