Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello QlikView Experts,
I am new to QlikView and developing an application to load sales data for one company.
The scenario I am working on is this:-
There is order_header table that mainly contains fields: orderdate, amount, id, itemname. Using the various date functions I have derived Year, Month Date from this field. (Right now not using a Separate Calendar Table). Now, I have one small table : Season that contains 4 columns:- Year, SeasonType, SeasonStartDate and SeasonCloseDate.
As you see the two tables are linked on the column: Year.
Now, I want to be able to get the sum or amount for the selected year but for orderdate >= SeasonStartDate and orderdate <= SeasonCloseDate. None of the below 3 Set expression I came up with is working. I am sure I am missing something here.
Sum({$<Year={"$(=max(Year))"},SeasonType = {'Summer'}, SeasonStartDate = {"$(=max(SeasonStartDate))"},SeasonCloseDate = {"$(=max(SeasonCloseDate))"}>}amount)
Sum({$<Year={"$(=max(Year))"},SeasonType = {'Summer'}, SeasonStartDate = {"$(=(SeasonStartDate))"},SeasonCloseDate = {"$(=(SeasonCloseDate))"}>}amount)
Sum({$<Year={"$(=max(Year))"},SeasonType = {'Summer'}, OrderDate={">=$(=min(SeasonStartDate)) <=$(=min(SeasonCloseDate))"}>}amount)
(not sure if we can we even compare OrderDate field directly to the SeasonStartDate ? as in the above expression?)
I have tried some other set expressions too but in vain.
Hope my explanation is clear. Any ideas please?
tx
Sunny
Hi Sunny
If I have understood you correctly there is a very useful function in QlikView called IntervalMatch that allows you to map a date into a set of date ranges, i.e. to determine which of a set of start and end dates a particular date fits in between.
In approaching your issue I created some test data so I will go through what I did and show you the result in the hope I have understood what you need correctly.
Step 1: Load the raw date
I created two tables, each one on a separate sheet in an Excel file, although you could load this data from any source. I used the field names you had mentioned in your question for clarity. The first table holds the raw transactional data for the amounts and the second holds the key dates for the seasons.
Table 1: Raw Data table called order_header
orderdate | amount | id | itemname |
23/12/2014 | 50 | 1 | a |
01/04/2015 | 100 | 2 | a |
01/06/2015 | 25 | 3 | c |
01/07/2015 | 50 | 4 | a |
01/08/2015 | 269 | 5 | b |
01/09/2015 | 258 | 6 | b |
01/10/2015 | 24 | 7 | c |
01/12/2015 | 366 | 8 | a |
23/12/2015 | 125 | 9 | a |
01/04/2016 | 426 | 10 | b |
01/06/2016 | 142 | 11 | b |
01/07/2016 | 568 | 12 | c |
01/08/2016 | 14 | 13 | a |
01/09/2016 | 125 | 14 | b |
01/10/2016 | 45 | 15 | c |
01/12/2016 | 135 | 16 | a |
Table 2: Key season dates table called Season
SeasonType | SeasonStartDate | SeasonCloseDate |
Winter | 21/12/2014 | 19/03/2015 |
Spring | 20/03/2015 | 20/06/2015 |
Summer | 21/06/2015 | 21/09/2015 |
Autumn | 22/09/2015 | 20/12/2015 |
Winter | 21/12/2015 | 19/03/2016 |
Spring | 20/03/2016 | 20/06/2016 |
Summer | 21/06/2016 | 21/09/2016 |
Autumn | 22/09/2016 | 20/12/2016 |
Step 2: Load these two tables into QlikView and process the data.
I have added the complete load script below and have annotated it to explain what each part does.
//Step 1: Load the raw transactional data
order_header:
LOAD
orderdate,
amount,
id,
itemname,
Month(orderdate) as Month,
Year(orderdate) as Year
FROM
[orders.xlsx]
(ooxml, embedded labels, table is order_header);
//Step 2: Load the key dates relating to the seasons. Create a composite key using the start and end dates to match with later on.
Season:
LOAD
SeasonType,
SeasonStartDate,
SeasonCloseDate,
Date(SeasonStartDate,'DD/MM/YYYY') & '-' & Date(SeasonCloseDate,'DD/MM/YYYY') as COMPKEY
FROM
[orders.xlsx]
(ooxml, embedded labels, table is season);
//Step 3: Use the interval match function to determine in which date range/season each order date falls. Join these dates back to the transactional data so that all the dates are in one table.
IntervalMatch:
left join (order_header)
IntervalMatch (orderdate)
Load
SeasonStartDate as Start,
SeasonCloseDate as end
Resident Season;
//Step 4: Create the date key in the order_header table in preparation for joining in the season type values
left join (order_header)
Load
id,
Date(Start,'DD/MM/YYYY') & '-' & Date(end,'DD/MM/YYYY') as COMPKEY
Resident order_header;
//Step 5: Join the season type values to the order_header table
left join (order_header)
Load
COMPKEY,
SeasonType
Resident Season;
//Step 6: Drop the season table as it is no longer needed
Drop table Season;
Step 3: Create a pivot chart in QlikView.
Dimensions are:
Year |
SeasonType |
Month |
orderdate |
Start |
end |
Expression is Sum(amount)
The end result will give you this pivot table: (All dimensions expanded)
As you can see this now aggregates the data by season in each year and by the year. To get the chart to display the values in the correct order you need to set a few sort options in the pivot table's properties.
Sort Options:
Set Year as Numeric Value ascending
Set SeasonType as Expression and enter the following in the expression editor for the sort range
If(SeasonType = 'Spring',1,
If(SeasonType = 'Summer',2,
If(SeasonType = 'Autumn',3,
4)))
Set Month as Numeric Value ascending
I hope that helps with your query.
Kind regards
Steve