How to pass value of tab1.field1 to tab2.field2 in a set expression

[#1]

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

[#2]

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