topic Re: Sum expression returns 0 in App Development

Sum expression returns 0

mtabernad — Sat, 16 Nov 2024 03:55:53 GMT

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.

Any ideas?

Thank you in advance.

Re: Sum expression returns 0

jaibau1993 — Wed, 04 Dec 2019 14:46:22 GMT

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.

Re: Sum expression returns 0

mtabernad — Wed, 04 Dec 2019 16:46:07 GMT

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:

Thank again.

Re: Sum expression returns 0

jaibau1993 — Thu, 05 Dec 2019 07:33:41 GMT

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.

Re: Sum expression returns 0

mtabernad — Thu, 05 Dec 2019 09:20:53 GMT

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.

Re: Sum expression returns 0

jaibau1993 — Thu, 05 Dec 2019 09:30:26 GMT

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.

Re: Sum expression returns 0

jonathandienst — Thu, 05 Dec 2019 09:54:44 GMT

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.

Re: Sum expression returns 0

mtabernad — Thu, 05 Dec 2019 09:59:16 GMT

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.

Re: Sum expression returns 0

mtabernad — Thu, 05 Dec 2019 11:45:24 GMT

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!