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.

[sunny_talwar · Accepted solution]

Try this

Prev Numer

Sum(Aggr(If(Max(TOTAL <surveyor_ID> survey_date) = survey_date and previous_score = 1, 1, 0), surveyor_ID, survey_date, survey_question))

Prev Denom

Sum(Aggr(If(Max(TOTAL <surveyor_ID> survey_date) = survey_date and Match(previous_score, 0, 1), 1, 0), surveyor_ID, survey_date, survey_question))

Curr Numer

Sum(Aggr(If(Max(TOTAL <surveyor_ID> survey_date) = survey_date and score = 1, 1, 0), surveyor_ID, survey_date, survey_question))

Curr Denom

Sum(Aggr(If(Max(TOTAL <surveyor_ID> survey_date) = survey_date and Match(score, 0, 1), 1, 0), surveyor_ID, survey_date, survey_question))

[Romi]

Without trying it out, I wanted to also let you know the reason for bringing the tank column into the equation (pulled from source data) ... I guess I can easily change the tank column to output a value just besides the rank to say, date appended with rank or something. But the thing is that I found some cases in the data where there were multiple surveys done for the same surveyor on the same date ... in which case, I wanted the maximum of the survey IDs to be picked up.

Thanks a lot again.

[sunny_talwar]

So, are you saying that there has been places where the above expression hasn't worked for you?

[Romi]

Sorry to keep you waiting for a few ... I am yet to test it out but I modified the formula to include the use of tank in the following manner...

Prev Numer

Sum(Aggr(If(Max(TOTAL <surveyor_ID> survey_date) = survey_date and Min(TOTAL <surveyor_ID> tank) = tank and previous_score = 1, 1, 0), surveyor_ID, tank, survey_date, survey_question))

Prev Denom

Sum(Aggr(If(Max(TOTAL <surveyor_ID> survey_date) = survey_date and Min(TOTAL <surveyor_ID> tank) = tank and Match(previous_score, 0, 1), 1, 0), surveyor_ID, tank, survey_date, survey_question))

Curr Numer

Sum(Aggr(If(Max(TOTAL <surveyor_ID> survey_date) = survey_date and Min(TOTAL <surveyor_ID> tank) = tank and score = 1, 1, 0), surveyor_ID, tank, survey_date, survey_question))

Curr Denom

Sum(Aggr(If(Max(TOTAL <surveyor_ID> survey_date) = survey_date and Min(TOTAL <surveyor_ID> tank) = tank and Match(score, 0, 1), 1, 0), surveyor_ID, tank, survey_date, survey_question))

[sunny_talwar]

okay.. let us know what you find 🙂

[Romi]

Worked perfectly ... Thank you so very much, Sunny!

[sunny_talwar]

Awesome, Good job!!