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 & DataLevel & ItemNumber as StcDat_ItemKey,
Company & DataLevel & InvoiceNumber as HstDat_GLEntryKey,
Company & DataLevel & SellToCustomerNumber 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.
- 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
