Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
eelainevv
Contributor
Contributor

Aggr to calculate fields

Hi there, 

I am creating a pivot table with two dimensions (row = unique question, column = roles). The data is like below. I want to calculate an average score on questions 02 - 04 per question per role for all project IDs. The score is based on answers to questions and for Question 01 the score will always be 0 and should be always be excluded.

For example, the outcome for Role B for Question 02 would be (5+3)/2 = 4

Project IDQuestions TextAnswer score / Text
101Role A (0)
1025
1034
101Role B (0)
1025
101Role C (0)
1034
201None (0)
2020
2033
201Role B (0)
2023
2044

 

Can anyone help with me on the function and expression to use assuming I cannot alter the table? Thanks in advance.

Labels (2)
1 Solution

Accepted Solutions
eelainevv
Contributor
Contributor
Author

Thank you Anil for your response. Just to clarify I was making an example of the average number i was looking for given the role B for question 02. 

I ended up using a different dimension for a similar visual which solved the issue I had. I think the real challenge is the Role is in the same column of the measures I need to calculate. Without extracting the Role as a separate column, it's very difficult. 

Thank you for your input. I will keep in mind the match function for future usage. 

 

View solution in original post

2 Replies
Anil_Babu_Samineni

I don't know what are these numbers? (5+3)/2 = 4

But, as a description you can try like If(Match([Questions Text], '01'), 0, <Your expression here>)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
eelainevv
Contributor
Contributor
Author

Thank you Anil for your response. Just to clarify I was making an example of the average number i was looking for given the role B for question 02. 

I ended up using a different dimension for a similar visual which solved the issue I had. I think the real challenge is the Role is in the same column of the measures I need to calculate. Without extracting the Role as a separate column, it's very difficult. 

Thank you for your input. I will keep in mind the match function for future usage. 

 

View solution in original post