Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Quat2014:
LOAD
EmployeeID as EmpID,
SalesPerson,
Q1,
Q2,
Q3,
Q4
FROM [lib://Soure Data/Quota.xls]
(biff, embedded labels, header is 1 lines, table is [Quota 2014$]);
Quat2015:
LOAD
EmployeeID as EmpID,
Q1,
Q2,
Q3,
Q4
FROM [lib://Soure Data/Quota.xls]
(biff, embedded labels, header is 1 lines, table is [Quota 2015$]);
How can i find the sales in 2014 Quarte and 2015 Quarter.
Hi,
here it depends on what you need in terms of modelisation.
you can join your tables using the joining field EmpID and renaming the Quarter fields using the Year as a description, like this Q1_2015, Q2_2015 and Q1_2014, Q2_2014
example
Quat2014:
LOAD
EmployeeID as EmpID,
SalesPerson,
Q1 as ,Q1_2014
Q2 as Q2_2014,
Q3 as Q3_2014,
Q4 as Q4_2014
FROM [lib://Soure Data/Quota.xls]
(biff, embedded labels, header is 1 lines, table is [Quota 2014$]);
left join //or any other type of join
Quat2015:
LOAD
EmployeeID as EmpID,
Q1 as Q1_2015,
Q2 as Q2_2015,
Q3 as Q3_2015,
Q4 as Q4_2015
FROM [lib://Soure Data/Quota.xls]
(biff, embedded labels, header is 1 lines, table is [Quota 2015$]);
or you can concatenate if you don't want or can't rename your fields
example:
Quat2014:
LOAD
EmployeeID as EmpID,
SalesPerson,
Q1,
Q2,
Q3,
Q4
FROM [lib://Soure Data/Quota.xls]
(biff, embedded labels, header is 1 lines, table is [Quota 2014$]);
concatenate
Quat2015:
LOAD
EmployeeID as EmpID,
Q1,
Q2,
Q3,
Q4
FROM [lib://Soure Data/Quota.xls]
(biff, embedded labels, header is 1 lines, table is [Quota 2015$]);
Quat2014 does not have a SalesPerson field. That is why you get a synthetic key. If you can either exclude SalesPerson if that field is not relevant or include SalesPerson in the Quat2015 if it is available in the sheet in the Excel workbook.
You should also do a CrossTable load to unpivot the quarters into a Sales field - that will make analysis and building the dashboard much easier.
I would suggest something like this:
SALES:
CrossTable( Quarter, Sales, 3) LOAD
EmployeeID as EmpID,
SalesPerson,
2014 AS Year,
Q1,
Q2,
Q3,
Q4
FROM [lib://Soure Data/Quota.xls]
(biff, embedded labels, header is 1 lines, table is [Quota 2014$]);
CrossTable( Quarter, Sales, 3 ) LOAD
EmployeeID as EmpID,
SalesPerson,
2015 AS Year,
Q1,
Q2,
Q3,
Q4
FROM [lib://Soure Data/Quota.xls]
(biff, embedded labels, header is 1 lines, table is [Quota 2015$]);
Since the two tables have exactly the same field Qlik will do an auto concatenation into a single table named SALES.
In your app you can now use expressions like these:
Sum( {<Year={2015}>} Sales ) - Sum( {<Year={2014}>} Sales )
or
Sum( {<Year={2015},Quarter={'Q1'}>} Sales ) - Sum( {<Year={2014},Quarter={'Q1'}>} Sales )
Hi
You can add Dummy filed Year as mentioned above :
SALES:
CrossTable( Quarter, Sales, 3) LOAD
EmployeeID as EmpID,
SalesPerson,
2014 AS Year,
Q1,
Q2,
Q3,
Q4
FROM [lib://Soure Data/Quota.xls]
(biff, embedded labels, header is 1 lines, table is [Quota 2014$]);
CrossTable( Quarter, Sales, 3 ) LOAD
EmployeeID as EmpID,
SalesPerson,
2015 AS Year,
Q1,
Q2,
Q3,
Q4
FROM [lib://Soure Data/Quota.xls]
(biff, embedded labels, header is 1 lines, table is [Quota 2015$]);
Thanks,
Arvind Patil