Set Analysis sub query

[#1]

Hi,

I'm trying to solve a subquery case in the front end of QlikView.

I've a table with:

animal_ID

Lever_code

session_date

session_ID

Value

I have a straight table that displays this table  but I want a extra field/expression that also give me the value of the max(session_date) where the session_date < session_date (of the record) and the Lever_code ='RR'.  

Can someone solve this case in set analysis?

Thx for helping!

br,

Koen

[SunilChauhan]

may be you require below code

sum({<session_date={"<=$(Max(session_date))"}, Lever_code ={'RR'} >} Value)

hope this helps

[#3]

thx for your reply but that's not the solution for my case.

the session date of the sub query should be the max (session date) of a set of data that gives me all the session dates that are smaller then the record session date and have a Lever_code = 'RR'

session_date < record_session_date and has Lever_code={'RR'} --> from this set of session_dates I need the value of the max(session_date)

I'm not sure it's possible with set analysis, maybe I need to load an extra island table (to compare the fields)

[jonathandienst]

Koen

Not sure what you mean with the first condition. What are you comparing session_date with? Is session_date a dimension and you are comparing with that dimension?

If that is the case, I think you might need an island table to get the results you are after. I suggest that you post your model, or a sample.

Regards

Jonathan

Edit -- missed your second post

[SunilChauhan]

try this

only({<session_date={"<$((record_session_date ))"}, Lever_code ={'RR'} >} session_date)

hope this helps

[#6]

in attachment I've a test example .  This example is working with an mirror island table of the main data.   I just wonder I can create my extra expression in my straight table based on a sub query of the same data.

thx for helping!