topic Re: Help with simple set analysis- multiple values in QlikView

Help with simple set analysis- multiple values

Fri, 13 Mar 2015 18:56:04 GMT

Hello,

I was wondering if someone could help me in setting a variable that sums the scores for a group of questions. Right now my expression looks like-

=sum({$<answer_id = 'question_31' or answer_id = 'question_32' or answer_id = 'question_33' or answer_id = 'question_34' or answer_id ='question_35' or answer_id = 'question_36' or answer_id = 'question_37' or answer_id = 'question_115' or answer_id = 'question_116' or answer_id = 'question_38' or answer_id = 'question_39' or answer_id = 'question_40' or answer_id = 'question_44'>}answer_score)

So basically for the following questions sum the scores. Any ideas on what I am doing wrong? I've tried using an if(sum) statement but maybe my syntax was off?

Re: Help with simple set analysis- multiple values

fred_s — Fri, 13 Mar 2015 23:02:37 GMT

Hi Patrick,

If Possible you could consider adding an extra field to you data, fe 'Questiontype'.

For example, questions 31, 37, 38, 115, etc are 'Math questions' and you'd like

to sum these Math Questions, all you need to do is a

=sum( {<Questiontype={'Math'}>} answer_score).

Grtz. Fred

Re: Help with simple set analysis- multiple values

Anonymous — Sat, 14 Mar 2015 01:39:18 GMT

I really like Fred's idea and suggest to ago with it but if for some reason you prefer not to group your questions, you can modify your expression using proper syntax. Example from help file:

sum( {$<Year = {2000}, Region = {US, SE, DE, UK, FR}>} Sales)

So in your case something like that (did not want to type all your IDs but you will get an idea:

=sum({$<answer_id = {question_31,question_32,question_33}>} answer_score)

It is easy to forget exact syntax, so what I do myself, I hit F1 from client, search for "set analysis" and you will see plenty helpful examples like the one above.

Re: Help with simple set analysis- multiple values

sunny_talwar — Sat, 14 Mar 2015 02:34:20 GMT

Is there something unique about the options you want to sum. Like may be they belong to a particular subject then you can use a simpler formula.

=Sum({<Subject = {'Mathematics'}>} answer_score)

If not you can create a flag in the script which would give a flag 1 like this:

If(Match(answer_id, 'question_31', 'question_32', 'question_33', 'question_34', 'question_35', 'question_36', 'question_37', 'question_115', 'question_116', 'question_38', 'question_39' , 'question_40', 'question_44'), 1, 0) as Flag

and then use it in your set analysis as follows:

=Sum({<Flag= {1}>} answer_score)

HTH

Best,

Re: Help with simple set analysis- multiple values

Sat, 14 Mar 2015 04:01:15 GMT

the issue in your expression is in set 'or' logic does not work, you have to update your expression by

=sum({<answer_id ={ 'question_31' , 'question_32' , 'question_33' , 'question_34' ,'question_35' , 'question_36' ,'question_37' , 'question_115' , 'question_116' , 'question_38' , 'question_39' , 'question_40' , 'question_44')>}answer_score)

or you should create a group of such question in the Back end so your expression would not look large like below

If(Match(answer_id, 'question_31', 'question_32', 'question_33', 'question_34', 'question_35', 'question_36', 'question_37', 'question_115', 'question_116', 'question_38', 'question_39' , 'question_40', 'question_44'), Grp1, Grp2) as Group

and after reload you can change your expression like

sum({<Group={'Grp1'}>}answer_score)

similarly you can create your various question groups

Re: Help with simple set analysis- multiple values

Mon, 16 Mar 2015 15:11:53 GMT

Thanks for your suggestions. I got it with our help. Just a case of rookie syntax.

Re: Help with simple set analysis- multiple values

sunny_talwar — Mon, 16 Mar 2015 15:16:22 GMT

If you got what you wanted to achieve, I would suggest closing the thread by marking the correct answer.

Best,