topic Re: Need help with Set Analysis in QlikView

Need help with Set Analysis

Romi — Sun, 21 Apr 2019 14:11:45 GMT

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.

Re: Need help with Set Analysis

Romi — Tue, 23 Apr 2019 01:30:09 GMT

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.

Re: Need help with Set Analysis

Romi — Tue, 23 Apr 2019 01:37:20 GMT

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)

Re: Need help with Set Analysis

sunny_talwar — Tue, 23 Apr 2019 12:30:50 GMT

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?

Re: Need help with Set Analysis

Vengatesh — Tue, 23 Apr 2019 12:50:11 GMT

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)

Re: Need help with Set Analysis

Romi — Tue, 23 Apr 2019 17:21:48 GMT

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.

Re: Need help with Set Analysis

Romi — Tue, 23 Apr 2019 17:24:20 GMT

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

Re: Need help with Set Analysis

sunny_talwar — Tue, 23 Apr 2019 17:39:57 GMT

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?

Re: Need help with Set Analysis

Romi — Tue, 23 Apr 2019 17:55:43 GMT

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.

Re: Need help with Set Analysis

Romi — Tue, 23 Apr 2019 17:59:42 GMT

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.

Re: Need help with Set Analysis

sunny_talwar — Tue, 23 Apr 2019 18:18:03 GMT

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))

Re: Need help with Set Analysis

Romi — Tue, 23 Apr 2019 18:24:13 GMT

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.

Re: Need help with Set Analysis

sunny_talwar — Tue, 23 Apr 2019 18:31:55 GMT

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

Re: Need help with Set Analysis

Romi — Tue, 23 Apr 2019 19:11:27 GMT

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))

Re: Need help with Set Analysis

sunny_talwar — Tue, 23 Apr 2019 19:16:26 GMT

okay.. let us know what you find 🙂

Re: Need help with Set Analysis

Romi — Tue, 23 Apr 2019 19:46:33 GMT

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

Re: Need help with Set Analysis

sunny_talwar — Tue, 23 Apr 2019 19:50:24 GMT

Awesome, Good job!!