Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik Community
I would like to calculate average scores across departments.
So a table that looks like this.
deptName | avgScore |
dept1 | 1.4 |
dept2 | 2.6 |
dept3 | 0.7 |
dept5 | 2.0 |
My table consists of a field [deptName] and a meassure avg(deptScore) but the meassure returns NULL across all departments.
Any advice is appretiaceted.
/Casper
it looks like you are loading data as text. You may want to check:
cheers
num(deptScore) as NumDeptScore
AVG(NumDeptScore)
Edit: You are correct. It was a formatting issue with regards to English/Danish decimal point separator.
I replaced the decimal point and it now works.
Original reply: deptScore is already a number.
it looks like you are loading data as text. You may want to check:
cheers
@CasperQlik - can you show your field details in data model viewer? Is it loaded as numerical tag?
You are correct. I discovered it just before reading your reply. The decimal delimiter was wrong and that formatted my value as a text. I tried with num() first but that did not work as you noted. I used the replace function and changed the delimiter and that automatically formatted my value to a number. The avg() function now return the expected result.
Your intuition is correct as I replied to your previous post. The value is in fact loaded as text.
i suggest to study functions: num() only applies mask on the value which is already number (The function takes the numeric value of the input expression and generates a string representing the number.) so it has different purpose. num# function would be the one you could use, but using replace() can do the job too in this case.
cheers