Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
How exactly do you plan to show the Quota Daily? Equally divide a single Quarter amount among all dates or is there a complex logic that you like to implement?
Hi Sunny,
Not sure, that is why I posted the question. If daily is not possible, is it possible to compare on Monthly, Quarterly and Yearly basis?
I guess I can help you frame your requirement into a code, but I can't tell you what you requirement should be? I mean if you just want a very basic daily comparison, I would suggest to equally divide quarterly data, but a more sophisticated model can be created based on past data. It all depends on what your audience want.
Hi Sunny,
I'm upskilling myself on Qlik and have a quite comprehensive data model and a lot of requirements, amongst which is the following word for word request:
"The metrics to be included are Sales (versus the previous year’s sales and versus Quota), Margin % and On-Time shipments. The variance from the previous year Sales and current year Quota must also be displayed"
It doesn't specify if it needs to be on daily or monthly and etc. basis so I just guessed...
I also thought about equally dividing quarterly data.
I researched a bit and came to a conclusion that I need to do a left join maybe on create a link table with the help of common fields.
The problem is that there are so many suggestions and sometime I get confused which approach would be the correct one.
Please advise.
hi there,
you can use canonical calendar to handle this situation -
Please use below code in your script and apply aggregation on measures
For quota measures apply aggr by QuotaDate
For order measures apply aggr by OrderDate
DateBridge:
QuotaDate,
QuotaDate as Date
Resident Quota;
Concatenate
OrderDate,
OrderDate as Date
Resident Sales_Order_Header;
minmaxdates:
Load
min(Date) as Mindate,
max(Date) as Maxdate
Resident DateBridge;
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 Date,
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;
thanks,
Rahul R
Thank you Rahul,
Let me give it a try!
HI Rahul,
So I implemented your suggestion but now I have a loop. What do I do with it?
Maybe I should try to implement this solution?
https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578
Ulyana, there is one other post that may be useful too, but good catch on the one you found! 🙂
https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130
Hopefully between the two of them you can get something working...
Regards,
Brett