Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Reply
Not applicable
Author

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

   

orderdateamountiditemname
23/12/2014501a
01/04/20151002a
01/06/2015253c
01/07/2015504a
01/08/20152695b
01/09/20152586b
01/10/2015247c
01/12/20153668a
23/12/20151259a
01/04/201642610b
01/06/201614211b
01/07/201656812c
01/08/20161413a
01/09/201612514b
01/10/20164515c
01/12/201613516a

 

Table 2: Key season dates table called Season

   

SeasonTypeSeasonStartDateSeasonCloseDate
Winter21/12/201419/03/2015
Spring20/03/201520/06/2015
Summer21/06/201521/09/2015
Autumn22/09/201520/12/2015
Winter21/12/201519/03/2016
Spring20/03/201620/06/2016
Summer21/06/201621/09/2016
Autumn22/09/201620/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