Good day everyone,
I wonder if someone could help me with mixed granularity issue in Qlikview:
I have a model with the following tables:
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Sales_Order_Detail:
LOAD *,
If([Shipping Status Days] <= 0, 'OnTime', 'Late') as [Shipping Status],
StandardCost * OrderQty as Cost;
SQL SELECT CarrierTrackingNumber,
DueDate,
OrderQty,
ProductID as Product_ID,
SalesOrderDetailID,
SalesOrderID,
ShipDate,
SpecialOfferID,
UnitPrice,
UnitPriceDiscount,
(UnitPriceDiscount * 100) & ' %' as UnitPriceDiscountPercent,
OrderQty * UnitPrice as [Orignal Amount],
[Orignal Amount] - ( UnitPriceDiscount * [Orignal Amount]) as [Sales Amount],
ShipDate - DueDate as [Shipping Status Days]
FROM SalesOrderDetail;
Sales_Order_Header:
SQL SELECT
AccountNumber as Acc_No,
BillToAddressID as Address_ID,
CustomerID as Customer_ID,
Freight,
LocalCurrency,
OnlineOrderFlag,
OrderDate,
PurchaseOrderNumber,
RevisionNumber,
SalesOrderID,
SalesOrderNumber,
ShipMethodID,
ShipToAddressID,
Status,
TaxAmt,
TerritoryID as Territory_ID
FROM SalesOrderHeader;
Sales_Territory_History:
SQL SELECT
BusinessEntityID as Employee_Business_Entity_ID,
EndDate as STH_End_Date,
StartDate as STH_Start_Date,
TerritoryID as Territory_ID
FROM SalesTerritoryHistory;
Employee:
SQL SELECT
BaseRate,
BirthDate,
CurrentFlag,
DepartmentName,
EmailAddress,
EmergencyContactName,
EmergencyContactPhone,
EmployeeBusinessEntityID as Employee_Business_Entity_ID,
EndDate,
FirstName,
Gender,
HireDate,
LastName,
FirstName & ' ' & LastName as [Sales Person],
MaritalStatus,
MiddleName,
ParentEmployeeBusinessEntityID,
PayFrequency,
Phone,
SalariedFlag,
SalesPersonFlag,
SalesTerritoryKey,
SickLeaveHours,
StartDate,
Status as Employee_Status,
Title,
VacationHours
FROM Employee;
Quota:
LOAD QuotaDate,
'Q' & Ceil(Month(QuotaDate)/3) as QuotaQuarter,
SalesPerson as Employee_Business_Entity_ID,
Quota as [Quota Amount]
FROM
[C:\Users\U.Koncheva\Documents\Qlikview\Betsys Bikes\Data\Application Data New.xlsx]
(ooxml, embedded labels, table is Quota);
MasterCalendar
minmaxdates:
Load
min(OrderDate) as Mindate,
max(OrderDate) as Maxdate
Resident Sales_Order_Header;
let vMindate = num(Peek('Mindate', 0, 'minmaxdates'));
let vMaxdate = num(Peek('Maxdate', 0, 'minmaxdates'));
drop Table minmaxdates;
DateRanges:
LOAD
$(vMindate) + IterNo()-1 as DateNum,
Date($(vMindate) + IterNo() -1 ) as TempDate
AutoGenerate 1 While $(vMindate) + IterNo() -1 <= $(vMaxdate);
MasterCalendar:
LOAD
TempDate as OrderDate,
Day(TempDate) as Day,
Week(TempDate) as Week,
Date(MonthStart(TempDate), 'YYYY MMM') as MonthName,
Month(TempDate) as Month,
num(Month(TempDate)) as MonthNum,
'Q' & Ceil(Month(TempDate)/3) as Quarter,
Year(TempDate) as Year,
if(Month(TempDate) >= 7, Year(TempDate) + 1, Year(TempDate)) as FinYear,
if(Month(TempDate) >= 7, Month(TempDate) - 6, Month(TempDate)+ 6) as FinPeriod,
if(Month(TempDate) >= 7, 'Q' & Ceil((Month(TempDate) - 6)/3), 'Q' & Ceil((Month(TempDate) + 6)/3)) as FinQuarter
Resident DateRanges order by TempDate asc;
drop Table DateRanges;
-----------------------------------------------------------------------------------------------------------------------------------------------------
I can report on Sales Amount by OrderDate, which is on daily basis, and separately on Quota Amount (Target) by QuotaDate, which is on quarterly basis.
But I would like to compare Sales Amount vs Quota Amount by day, month, quarter and year.
Please advise what would be the best way to do it?
