topic Re: Canonical Calendar Challenges in QlikView

Canonical Calendar Challenges

Anonymous — Mon, 28 Aug 2017 14:44:56 GMT

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.

Re: Canonical Calendar Challenges

sunny_talwar — Mon, 28 Aug 2017 14:49:48 GMT

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?

Re: Canonical Calendar Challenges

Anonymous — Mon, 28 Aug 2017 14:54:24 GMT

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.

Re: Canonical Calendar Challenges

sunny_talwar — Mon, 28 Aug 2017 14:59:29 GMT

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);

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)

Re: Canonical Calendar Challenges

Anonymous — Mon, 28 Aug 2017 15:21:17 GMT

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

Re: Canonical Calendar Challenges

sunny_talwar — Mon, 28 Aug 2017 15:26:50 GMT

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

Re: Canonical Calendar Challenges

Anonymous — Tue, 29 Aug 2017 09:22:10 GMT

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

Re: Canonical Calendar Challenges

Anonymous — Wed, 30 Aug 2017 11:54:04 GMT

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?

Re: Canonical Calendar Challenges

Anonymous — Tue, 05 Sep 2017 12:21:46 GMT

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

Re: Canonical Calendar Challenges

Anonymous — Wed, 20 Sep 2017 06:42:42 GMT

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

Re: Canonical Calendar Challenges

hic — Wed, 20 Sep 2017 08:14:11 GMT

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?

HIC

Re: Canonical Calendar Challenges

Anonymous — Wed, 20 Sep 2017 08:45:22 GMT

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

hic — Wed, 20 Sep 2017 09:08:54 GMT

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

Re: Canonical Calendar Challenges

Anonymous — Wed, 20 Sep 2017 11:30:45 GMT

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

hic — Wed, 20 Sep 2017 11:39:06 GMT

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

Re: Canonical Calendar Challenges

Anonymous — Wed, 20 Sep 2017 13:34:00 GMT

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