AVG() returns NULL

[CasperQlik]

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

[SunilChauhan]

num(deptScore) as NumDeptScore

AVG(NumDeptScore)

[CasperQlik]

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. 

[Lech_Miszkiewicz · Accepted solution]

it looks like you are loading data as text. You may want to check:

• decimal delimiter
• or use num# function when loading data (do not confuse it with num() function which has different purpose and often is wrongly suggested!) to convert text to number: https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/Interpr...

cheers

[Lech_Miszkiewicz]

@CasperQlik - can you show your field details in data model viewer? Is it loaded as numerical tag?

[CasperQlik]

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. 

[CasperQlik]

Your intuition is correct as I replied to your previous post. The value is in fact loaded as text.

[Lech_Miszkiewicz]

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.

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/Formatt...

cheers