Need help with Set Analysis

[Romi]

Data in question...

LOAD * INLINE [
surveyor_ID, survey_date, survey_key, survey_question, score, previous_score
ABCD1001, 4/25/2018, 74984, Are you taller than 6 feet?, 0,
ABCD1001, 4/25/2018, 74984, Do you like the songs in album X?, 1,
ABCD1001, 4/25/2018, 74984, Are you excited about the new product?, 0,
ABCD1001, 4/25/2018, 74984, Do you think our new menu additions have improved?, 0,
ABCD1001, 4/25/2018, 74984, Has the new location impacted your dining experience?, 0,
ABCD1001, 4/25/2018, 74984, Are you usually excited to know more about new movies?, 1,
ABCD1001, 4/25/2018, 74984, Do you support the move for cleaner environment?, 1,
ABCD1001, 4/25/2018, 74984, Do you feel confused with long-winded questions?, 0,
ABCD1001, 4/25/2018, 74984, Do you feel sleepy after lunch?, 0,
BCDE1002, 1/15/2019, 98421, Are you taller than 6 feet?, 0,
BCDE1002, 1/15/2019, 98421, Do you like the songs in album X?, 0,
BCDE1002, 1/15/2019, 98421, Are you excited about the new product?, 1,
BCDE1002, 1/15/2019, 98421, Do you think our new menu additions have improved?, 0,
BCDE1002, 1/15/2019, 98421, Has the new location impacted your dining experience?, 0,
BCDE1002, 1/15/2019, 98421, Are you usually excited to know more about new movies?, 0,
BCDE1002, 1/15/2019, 98421, Do you support the move for cleaner environment?, 0,
BCDE1002, 1/15/2019, 98421, Do you feel confused with long-winded questions?, 0,
BCDE1002, 1/15/2019, 98421, Do you feel sleepy after lunch?, 0,
CDEF1003, 6/6/2018, 78627, Are you taller than 6 feet?, 0, 0
CDEF1003, 6/6/2018, 78627, Do you like the songs in album X?, 0, 0
CDEF1003, 6/6/2018, 78627, Are you excited about the new product?, 1, 1
CDEF1003, 6/6/2018, 78627, Do you think our new menu additions have improved?, 0, 0
CDEF1003, 6/6/2018, 78627, Has the new location impacted your dining experience?, 0, 0
CDEF1003, 6/6/2018, 78627, Are you usually excited to know more about new movies?, 0, 0
CDEF1003, 6/6/2018, 78627, Do you support the move for cleaner environment?, 0, 0
CDEF1003, 6/6/2018, 78627, Do you feel confused with long-winded questions?, 0, 0
CDEF1003, 6/6/2018, 78627, Do you feel sleepy after lunch?, 0, 0
CDEF1003, 10/18/2018, 88192, Are you taller than 6 feet?, 0, 0
CDEF1003, 10/18/2018, 88192, Do you like the songs in album X?, 0, 0
CDEF1003, 10/18/2018, 88192, Are you excited about the new product?, 1, 1
CDEF1003, 10/18/2018, 88192, Do you think our new menu additions have improved?, 0, 0
CDEF1003, 10/18/2018, 88192, Has the new location impacted your dining experience?, 0, 0
CDEF1003, 10/18/2018, 88192, Are you usually excited to know more about new movies?, 0, 0
CDEF1003, 10/18/2018, 88192, Do you support the move for cleaner environment?, 0, 0
CDEF1003, 10/18/2018, 88192, Do you feel confused with long-winded questions?, 0, 0
CDEF1003, 10/18/2018, 88192, Do you feel sleepy after lunch?, 0, 0
CDEF1003, 3/22/2019, 98864, Are you taller than 6 feet?, 0, 0
CDEF1003, 3/22/2019, 98864, Do you like the songs in album X?, 0, 0
CDEF1003, 3/22/2019, 98864, Are you excited about the new product?, 1, 1
CDEF1003, 3/22/2019, 98864, Do you think our new menu additions have improved?, 0, 0
CDEF1003, 3/22/2019, 98864, Has the new location impacted your dining experience?, 0, 0
CDEF1003, 3/22/2019, 98864, Are you usually excited to know more about new movies?, 0, 0
CDEF1003, 3/22/2019, 98864, Do you support the move for cleaner environment?, 0, 0
CDEF1003, 3/22/2019, 98864, Do you feel confused with long-winded questions?, 0, 0
];

Desired Output...

For every survey question, it should only consider the latest survey for each surveyor (user can of course choose a date range to change the time window in which case, the latest survey for each surveyor should appropriately change) and display the following...
    - count of surveys that have scores of 1 as the current_numerator
    - count of surveys that have scores of either a 0 or 1 (but no blank or anything else) as the current_denominator

My attempts (that are not working)...

curr_numer = Count(DISTINCT {$<survey_key = {"=Max(survey_date) = Max(Aggr(Max(survey_date), surveyor_ID, survey_question))"}, score = {1}>} survey_key)
curr_denom = Count(DISTINCT {$<survey_key = {"=Max(survey_date) = Max(Aggr(Max(survey_date), surveyor_ID, survey_question))"}, score = {0,1}>} survey_key)

Another Request...

Is there some better book or detailed reference to the advanced set analysis? Besides the few examples on the QlikView online reference, I didn't find much.

[Romi]

Found this nice page for almost all references for SET ANALYSIS concepts under one roof ... http://livingqlikview.com/livingqlik-roots-the-ultimate-qlikview-set-analysis-reference/ but it still does not help me completely because of the complicated problem that I'm trying to solve ... or I'm not looking at it correctly.

[Romi]

In the meantime, I went ahead and added a column named tank for ranking based on the data coming from the database in following manner...

rank() OVER(PARTITION BY surveyor_ID ORDER BY survey_date DESC, survey_key DESC) AS tank

Changed my expressions using that and they're giving me OH-SO-CLOSE results for a sample of 5K rows. I'm getting the result off by 1 (ONE!!) for the previous score's numerator calculation ... rest are showing good for this sample of 5K rows.

prev_numerator = Count(DISTINCT {$<survey_key = {"=Min(Aggr(Min(tank), surveyor_ID)) = Min(Aggr(Min(tank), surveyor_ID, survey_question))"}, previous_score = {1}>} survey_key)

[sunny_talwar]

Am not really sure I understand the final output you are hoping to get based on the input provided.... Would you be able to let us know what the final number for numerator and denominator you are hoping to get?

[Vengatesh]

I would suggest you to load only the latest records from the back end. so that you don't have to worry about set analysis.

Load *
Resident [Table Name]
Where surveyor_ID <> Previous (surveyor_ID)
Order by surveyor_ID, survey_date desc;

Exp: Count(Distinct {<score={1}>}survey_key)/Count(Distinct survey_key)

[Romi]

Thanks for looking into it and I do realize how confusing my post might have been. I cannot limit the data from the source to choose the latest survey because the user can change the duration of the survey and then, the latest survey will/can very quickly and easily change to a different one.

[Romi]

Thanks for looking into it and I do realize how confusing my post might have been. I should have pasted some pictures along. Better late than never ... here they are ... expectation (depicted in Excel) versus reality (what's happening on QlikView).

Expectation

Reality

[sunny_talwar]

I guess my expression matchs the result that you are getting currently. Based on what we are getting, seems like we are only off for Do you feel sleepy after lunch? question, right?

Why would the curr Denom by 2 and not 3 for Do you feel sleepy after lunch?

I see three surveryor_ID and all three have a recent response of 0 for score. Would that not make it a total of 3?

[Romi]

Please allow me to express my gratitude once more (feeling very pleased and thankful for your time on my problem). The current denominator for the question "Do you feel sleepy after lunch?", will not be 3 because of the LATEST survey to be considered ONLY for getting the metrics calculated for each of the questions. If you filter out the Tank value = 1, you will see that. However, Tank value cannot always and blindly be 1 ... it can be whatever (but always, the minimum of the lot) depending on the survey period that the user chooses.

[Romi]

The latest survey for surveyor CDEF1003 (having survey key: 98864) didn't even have the question "Do you feel sleepy after lunch?" asked and answered ... is what I guess I was trying to say.