topic Re: sum returns 0 in QlikView

sum returns 0

diane_yu — Fri, 03 Apr 2015 15:18:36 GMT

Hello all,

I've created pivot chart to sum the Cost_per_Enc field. In the database, this field is a decimal (18,2). In expression, I coded

=sum(Cost_per_Enc). In straight table, it displays proper $$ amount. It seems like Qlik is treating this field as varchar. How do I convert them so I can sum the field?

Thank you for your help in advance!

Re: sum returns 0

Gysbert_Wassenaar — Fri, 03 Apr 2015 15:28:22 GMT

In straight table, it displays proper $$ amount.

Do you mean a table box? Or are you using it as a dimension in a straight table? A pivot table is a chart type, just like a straight table is. You can change one into the other by changing the chart type on the General tab of its properties window.

If the field is a decimal in the database, then Qlikview will load it a number too. So I doubt the values get turned into strings. Can you post a small qlikview document that demonstrates the problem?

Re: sum returns 0

MK_QSL — Fri, 03 Apr 2015 15:31:27 GMT

Load the field in script as

Num(FieldName) as FieldName

Re: sum returns 0

Peter_Cammaert — Fri, 03 Apr 2015 15:32:55 GMT

If the sum displays a proper $-amount in your straight table, then what is the problem you want to discuss?

If the sum displays a 0 value, then open the Table Viewer to check the content of the Cost_per_enc column in different rows of your internal table. Are the values right- or left-aligned? If the values are right-aligned, they exist as pure text only and cannot lead to a correct aggregation, probably due to some erroneous separator. Check whether your decimal separator is correct.

Re: sum returns 0

diane_yu — Fri, 03 Apr 2015 16:01:10 GMT

Hi Manish,

Thank you for your suggestion. I reload my data with num(Cost_Per_Enc) as Cost_Per_Enc and it did not work.

Re: sum returns 0

diane_yu — Fri, 03 Apr 2015 16:09:34 GMT

Hi Peter,

In straight table, Cost_Per_Enc column displays raw number and it is right aligned. I can format the money and it displays like currency but it won't sum it in Chart. I can sum the column in DB.

Thanks.

Re: sum returns 0

diane_yu — Fri, 03 Apr 2015 16:15:09 GMT

Hi Gysbert,

Yes I'm talking about table box. I changed the Cost_Per_Enc as currency and it display as currency in Table Box but it won't sum it up even after I reload the data with num(Cost_Per_Enc) as Cost_Per_Enc in Chart Properties.

I can't upload the file due to HIPAA issue.

Thanks.

Re: sum returns 0

Gysbert_Wassenaar — Fri, 03 Apr 2015 16:32:59 GMT

I changed the Cost_Per_Enc as currency and it display as currency in Table Box

Then the data is loaded as a number. Whatever the real problem is, it is not related to the data type. A data modeling issue is more likely. Please read this document: Preparing examples for Upload - Reduction and Data Scrambling

Re: sum returns 0

diane_yu — Fri, 03 Apr 2015 17:52:27 GMT

Here are my sample data.

Filtered list in Table Box:

DMIS_ID	Cost_Per_Enc	APPOINTMENT_STATUS
0001	$0.12	CANCEL
0001	$22.03	CANCEL
0001	$25.35	CANCEL
0001	$41.71	CANCEL
0001	$52.89	CANCEL
0001	$77.84	CANCEL
0001		CANCEL

Without Filter in Chart Properties:

DMIS_PARENT_ID	Count appointment IEN	Sum(CostPerEnc)
	11980319	$15,703.83
0047	206563	$0.00
0048	469341	$0.00
0049	463565	$0.00
0060	520708	$0.00
0064	74782	$0.00
0098	97736	$0.00
0105	315045	$0.00
0109	567743	$0.00
0110	888617	$0.00
-	8376219	$15,703.83

Re: sum returns 0

vvvvvvizard — Sat, 04 Apr 2015 18:25:03 GMT

use DMis id as ur dimension instead of dmis parent id

Re: sum returns 0

diane_yu — Mon, 06 Apr 2015 12:02:35 GMT

Good morning yusuf,

I changed to proper DMISID and it worked. Thank you.

Re: sum returns 0

ynomani8223 — Sun, 22 Sep 2019 06:13:54 GMT

I used the following set Analysis in Qlik Sense

sum({<color_code={"Red" }> } color_code) but getting 0 in color_code ( 4th column).

I should see only Red related rows. Where I am doing wrong?

Re: sum returns 0

swati_gupta — Mon, 30 Sep 2019 16:13:24 GMT

Could you please tell how did you solve this?