How to use fields from different tables in set expression

Thu, 18 Jun 2015 18:18:14 GMT

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?

Sunny

Re: How to use fields from different tables in set expression

oknotsen — Thu, 18 Jun 2015 20:39:22 GMT

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.

topic How to use fields from different tables in set expression in QlikView

How to use fields from different tables in set expression

Re: How to use fields from different tables in set expression