sum returns 0

[diane_yu]

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!

Di

[Gysbert_Wassenaar]

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?

[MK_QSL]

Load the field in script as

Num(FieldName) as FieldName

[Peter_Cammaert]

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.

[diane_yu]

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.

[diane_yu]

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.

[diane_yu]

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.

Di

[Gysbert_Wassenaar]

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

[diane_yu]

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

[vvvvvvizard · Accepted solution]

use DMis id as ur dimension instead of dmis parent id