0 Replies Latest reply: Nov 7, 2016 5:50 AM by Court van de Lisdonk RSS

    recaculate monthly amounts into week/day amounts

    Court van de Lisdonk

      Hello,

       

      I have a question/problem.  I designed a Qlikview Document SalesInvoice. In the script of dis document the FactTables SalesInvoice is loaded. There is a LineAmount which is used for the Actual Invoices   (LineNumber 00) and Budget (LineNumber 01,02….,12) So we have in our Table twelve records related to  Months  and Amounts.

      The script is as follows:

      SalesInvoice:
      LOAD
      *,
      CompanyNumber &' ' & CompanyName                      as    Company,
      CountryNumber &' ' & CountryName                  as    CountryID, CustomerNumber &' ' & CustomerName                      as    CustomerID;
      Load
      Company & DataLevel & Company                             as    CalculationsKey,
      Company & DataLevelItemNumber                       as    StcDat_ItemKey,
      Company & DataLevelInvoiceNumber                    as    HstDat_GLEntryKey,
      Company & DataLevelSellToCustomerNumber         as    StcDat_SellToCustomerKey,
      InvoiceNumber & InvoiceLineNumber                  as    InvoiceNumber_InvoiceNumberPosKey,

      Company                                     as    CompanyNumber,
      DataLevel,
      LoadingDate,
      InvoiceNumber,
      Num(1)                                                 as CounterInvoiceNumber,
      InvoiceLineNumber,                               // = Month for Budget         
      ItemNumber,    
      CustomerID                                         as CustomerNumber,
      DivisionID                                            as DivisionNumber,
      ProductGroupID                                            as ProductGroupNumber,
      SalesDistrictID                                      as SalesDistrictNumber,
      CustomerTypeID                                          as CustomerTypeNumber,
      CountryID                                      as CountryNumber,
      CountryGroupCode,
      MarketTypeCode,     
      LineAmount,
      SellToCustomerNumber,

      ApplyMap('PrimDim_Company', Company & DataLevel & Company, 'CompanyName NotFound')      as CompanyName,
      ApplyMap('PrimDim_Country', Company & DataLevel & CountryID, 'CountryName NotFound’)       as CountryName,
      ApplyMap('ThrdDim_CountryGroup', Company & DataLevel & CountryGroupCode,  'CountryGroupName NotFound')      as CountryGroupName
      ApplyMap('ThrdDim_MarketType', Company & DataLevel & MarketTypeCode,  'MarketTypeName NotFound') as MarketTypeName,

      if(DataLevel = 'Budget' and  InvoiceLineNumber > 0,LineAmount,NULL()) as BudgetAmount,
      if(DataLevel = 'Actual' and ( InvoiceLineNumber = 0 or  InvoiceLineNumber > 12),LineAmount,NULL()) as ActualAmount,
      if(DataLevel = 'Budget' and  InvoiceLineNumber > 0, MakeDate(Year (PostingDate),  [InvoiceLineNumber], 1),Date(PostingDate))as Date

      FROM [$(DATADIR)qvd\SalesInvoice.qvd] (qvd);

       

       

      With the IF-statement  I split Line-Amount into BudgetAmount and ActualAmount.  Now my chef want a view (Graphic or Table) with Week and Day amount for Actual and Budget. The ActualAmounts are not the problem because they have different  Postingdates. The BudgetAmounts have only one date (the first day) of the month. 

       

      Other problems are:

      the Amounts per Month are not equal, in summer the amounts are bigger as in winter.

      1. Not every day has an amount, the DayAmount shouldbe calculated  à BudgetAmount divided by 21, The WeekAmount is then (BudgetAmount divided by 21) multiply with 5.

      Question is now can I give my chef his view, so recalculate budget into days and how should I do that. Within the SalesInvoice load, create a new table CalculationedAmounts based on the MasterCalendar, resident SalesInvoice or should I calculate it with the expressions in the Dashboards, I think I need also the Autogenerate-function but I’m not sure. What is the best solution, I prefer a solution in the script above a DashboardSolution


      Thanks in advance