Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Ukrainian_chick
Contributor II
Contributor II

How to compare Sales and Targets if Sales are on daily basis and Targets on Quarterly basis

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?

Labels (2)
10 Replies
sunny_talwar

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?

Ukrainian_chick
Contributor II
Contributor II
Author

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?

sunny_talwar

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.

Ukrainian_chick
Contributor II
Contributor II
Author

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.

rathore01
Partner - Contributor III
Partner - Contributor III

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

Ukrainian_chick
Contributor II
Contributor II
Author

Thank you Rahul,

Let me give it a try!

Ukrainian_chick
Contributor II
Contributor II
Author

HI Rahul,

So I implemented your suggestion but now I have a loop. What do I do with it?

Ukrainian_chick
Contributor II
Contributor II
Author

Maybe I should try to implement this solution?

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578 

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.