Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
As i am new to QlikSense, i have a task to produce the graph and which takes data from the db.
DB Tables:
Survey
survey_id lender_offer_id answer_text
10 1 Strongly Agree
11 1 Agree
12 2 Excellent
13 2 Above Average
14 3 Strongly Agree
15 3 Agree
Here is my Script:
LOAD `survey_id`,
`lender_offer_id`,
`survey_question_id`,
`answer_text`;
SQL SELECT `survey_id`,
`user_id`,
`lender_offer_id`,
`batch_meta_data_id`,
`survey_question_id`,
`answer_text`
FROM SURVEY;
My Goal is to produce a bargraph that must produce a percentage to know how much percent of people opted to particular Option in the Survey.
But restriction here is i must take Strongly Agree and Excellent as Strongly Agree, Agree and Above Average as Agree by totalling.
For eg:
In the above table total 3 persons opted for Strongly Agree(Included Excellent) and 3 for Agree(included Above Average).
How can i do that using QlikSense.
I would change your second one as follow:
=if((Match(answer_text,'Strongly Agree','Excellent')),'Strongly Agree',if(Match(answer_text,'Agree','Above Average'),'Agree'))
First thing, I would create a new field in the script :
LOAD `survey_id`,
`lender_offer_id`,
`survey_question_id`,
'Strongly Agree' as Answer,
`answer_text`
where `answer_text` ='Strongly Agree' or `answer_text`='Excellent'
;
SQL SELECT `survey_id`,
`user_id`,
`lender_offer_id`,
`batch_meta_data_id`,
`survey_question_id`,
`answer_text`
FROM SURVEY;
LOAD `survey_id`,
`lender_offer_id`,
`survey_question_id`,
'Agree' as Answer,
`answer_text`
where `answer_text` ='Agree' or `answer_text`='Above Average'
;
SQL SELECT `survey_id`,
`user_id`,
`lender_offer_id`,
`batch_meta_data_id`,
`survey_question_id`,
`answer_text`
FROM SURVEY;
Now in your bar chart:
As dimension; choose your newly created field: Answer
As a measure:
count(lender_offer_id ) / count({1}total lender_offer_id )
and format it as %.
Thanks for ur reply,
But here i need to get the counts for only two questions out of many, ie users who opted those options in two questions.
And i need to change this in expressions,because i can't able to make any changes in script,it'll affect further charts.
Best Regards
That won't affect a thing, you could always use your exsting fields in other charts, and use only your new field in the bar chart you're willing to make :
If you have other answers, you can add Everything, like THIS. and do NOT CHANGE A THING in your other charts, they won't be affected.
LOAD `survey_id`,
`lender_offer_id`,
`survey_question_id`,
'Strongly Agree' as Answer,
`answer_text`
where `answer_text` ='Strongly Agree' or `answer_text`='Excellent'
;
SQL SELECT `survey_id`,
`user_id`,
`lender_offer_id`,
`batch_meta_data_id`,
`survey_question_id`,
`answer_text`
FROM SURVEY;
LOAD `survey_id`,
`lender_offer_id`,
`survey_question_id`,
'Agree' as Answer,
`answer_text`
where `answer_text` ='Agree' or `answer_text`='Above Average'
;
SQL SELECT `survey_id`,
`user_id`,
`lender_offer_id`,
`batch_meta_data_id`,
`survey_question_id`,
`answer_text`
FROM SURVEY;
LOAD `survey_id`,
`lender_offer_id`,
`survey_question_id`,
'Other as Answer,
`answer_text`
where `answer_text` <>'Strongly Agree' and `answer_text`<>'Excellent' and `answer_text` <>'Agree' and`answer_text`<>'Above Average'
;
SQL SELECT `survey_id`,
`user_id`,
`lender_offer_id`,
`batch_meta_data_id`,
`survey_question_id`,
`answer_text`
FROM SURVEY;
Now in your bar chart, If I fully understand, you want to divide by the count of these 2 answers (strongly agree, and agree?)
Do as follow:
As dimension; choose your newly created field: Answer
As a measure:
count(lender_offer_id ) / count({<Answer={'Agree','Strongly Agree'}>}total lender_offer_id )
and format it as %.
LOAD `survey_id`,
`lender_offer_id`,
`survey_question_id`,
'Other as Answer,
`answer_text`
where `answer_text` <>'Strongly Agree' and `answer_text`<>'Excellent' and `answer_text` <>'Agree' and`answer_text`<>'Above Average'
Why you are adding this,,can u pls explain it
I thought you had answers other than
Strongly Agree' ,'Excellent' ,'Agree' and 'Above Average' don't you?
And also i'm getting error saying:
The following error occurred:
Yea it's in other questions,,i mean to say after question 2.
Try as follow:
LOAD `survey_id`,
`lender_offer_id`,
`survey_question_id`,
'Strongly Agree' as Answer,
`answer_text`
;
SQL SELECT `survey_id`,
`user_id`,
`lender_offer_id`,
`batch_meta_data_id`,
`survey_question_id`,
`answer_text`
FROM SURVEY
where `answer_text` ='Strongly Agree' or `answer_text`='Excellent'
;
LOAD `survey_id`,
`lender_offer_id`,
`survey_question_id`,
'Agree' as Answer,
`answer_text`
;
SQL SELECT `survey_id`,
`user_id`,
`lender_offer_id`,
`batch_meta_data_id`,
`survey_question_id`,
`answer_text`
FROM SURVEY
where `answer_text` ='Agree' or `answer_text`='Above Average'
;
LOAD `survey_id`,
`lender_offer_id`,
`survey_question_id`,
'Other as Answer,
`answer_text`
;
SQL SELECT `survey_id`,
`user_id`,
`lender_offer_id`,
`batch_meta_data_id`,
`survey_question_id`,
`answer_text`
FROM SURVEY
where `answer_text` <>'Strongly Agree' and `answer_text`<>'Excellent' and `answer_text` <>'Agree' and`answer_text`<>'Above Average';
Some Sync error:
2:48:53 TT
Output cleared.
App successfully saved.