Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mtabernad
Partner - Creator
Partner - Creator

Sum expression returns 0

Hi all,

I am trying to sum my "cpu" field but it returns 0. I tried 'sum(num(cpu))' and also 'sum(cpu)' but it doesn't work. Plus, I changed my load script to "num(cpu) as cpu" but problem persists.

capture13.png

Any ideas?

Thank you in advance.

Labels (1)
8 Replies
jaibau1993
Partner - Creator III
Partner - Creator III

Hi!

Your field "cpu" is not being interpreted correctly as a number. The clue is that it is indented on the left in your table: it indicates it is a text.

Note that I said "Interpreted" but not "formated". Num() is a format function but you need to tell Qlik Sense how to interpret that "1.63". I bet the problem is in the dot: surely your language configuration uses comma as decimal separator! If so is right (check the system variable "DecimalSep" at the beginning of your load script), then try the following in the script side:

Load

      Num(Num#(cpu, '#.#','.',',')) as cpu

[...]

I use Num# to tell Qlik Sense how to interpret the input string as a number and then I use Num to make it look as a number (following the regional especifications).

Hope it helps!

Jaime.

mtabernad
Partner - Creator
Partner - Creator
Author

Hi Jaime,

Thank you for your reply.

I followed your tips and now the KPI shows something but info in table is wrong since it shows the character "?" as you can see in the following picture:

capture14.png

Thank again.

jaibau1993
Partner - Creator III
Partner - Creator III

Hi!

As far as I know, Qlik Sense do not use that '?' symbol when something does not work (QS just sets the wrong value as Null). Thus, I bet those '?' came as '?' from your data source. Whatsmore, since your KPI now shows a number, that means that there are numbers among the values of cpu!

Said so, load your cpu field as it is in your data source and explore its content. Remember that you should always perform an exploratory data analysis.

regards,

Jaime.

mtabernad
Partner - Creator
Partner - Creator
Author

Hi Jaime,

I looked for some info about "?" symbol displayed in a QS table and it seems to be a problem related to format again:

https://community.qlik.com/t5/QlikView-Connectors/Multiple-Question-mark-in-list-box/m-p/297156

It is solved adding the "text" function, but if I do that in load script: 

text(Num(Num#(cpu, '#.#','.',','))) as cpu

It is solved because data shows up in the table but in text format again and then the KPI sum(cpu) doesn't work (back to original problem of the post)

So if I use text format, info show up in table but not in the KPI. And if I use number format, info show up in KPI but not in the table.

cpu field does not contain any '?' in its source format, so it's a problem from Qlik.

I'm going to try to change the format in the source to number format. Anyway, new ideas are welcome.

Thank you Jaime, I appreciate your time.

 

 

jaibau1993
Partner - Creator III
Partner - Creator III

Quite weird, I had never listened about that '?'. If you can share a sample app maybe I can help you a bit more. Let me know how you finally solve it, otherwise.

Regards,

Jaime.

jonathandienst
Partner - Champion III
Partner - Champion III

Are you loading field cpu from more than one data source, or more than one load statement? As John W and I discovered when we looked at this a few years ago, a mix of text and numerics can sometimes confuse the type inference logic in QV/QS.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mtabernad
Partner - Creator
Partner - Creator
Author

I solved it changing the format in the SQL Server, but wasn't able to do it in QS. Now info show up in the right side of the QS table and the KPI is calculated properly.

Thank you.

Regards.

mtabernad
Partner - Creator
Partner - Creator
Author

No, I am loading this filed just from one SQL instance. Weird... Anyway I solved it changing the format in SQL Server instance.

Thank you anyway!