Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to use fields from different tables in 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 column 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
oknotsen
Master III
Master III

Try removing the quotes around the $(=max(Year)). You should not need those.

Assume both OrderDate and SeasonStartDate are effectively numbers (as dates are in Qlik), you can compare these different fields directly with each other.

No clue how many SeasonTypes you have, but if it is a low number, you could consider flagging all (Order)Dates for the specific seasons which might make your Set Analysis statements less complex.

Without having an actual look at (example) data, I fear I cant help you much more at this moment.

May you live in interesting times!