Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
use DMis id as ur dimension instead of dmis parent id
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?
Load the field in script as
Num(FieldName) as FieldName
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.
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.
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.
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
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
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 |
use DMis id as ur dimension instead of dmis parent id