15 Replies Latest reply: Sep 20, 2017 9:34 AM by Mike Seisbye RSS

    Canonical Calendar Challenges

    Mike Seisbye

      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.

        • Re: Canonical Calendar Challenges
          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?

            • Re: Canonical Calendar Challenges
              Mike Seisbye

              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

                  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)

              • Re: Canonical Calendar Challenges
                Mike Seisbye

                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
                    Henric Cronström

                    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

                      • Re: Canonical Calendar Challenges
                        Mike Seisbye

                        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
                            Henric Cronström

                            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