calculated field stuck being a whole number
I'm hoping this is just a simple thing I'm missing due to my lack of experience in Yellowfin.
In Calculated Field/Simple, I obtain the ratio of aggregations of a calculated field and a column, both of which are whole numbers:
SUM ( Eff_DTC2 ) / COUNT ( Case # )
I'd like the result to two decimal places, which it is, but the two are always ".00", which is not helpful. For example, with the numerator = 12001 and the denominator = 409, the result displays as 29.00 instead of 29.34. I even went back and changed the format of the two referenced fields to be 12001.00 & 409.00, but that didn't help either.
Do I need to explicitly fix two decimal places in the calculation? Something like:
DEC (SUM (Eff_DTC2),2) / DEC (COUNT (Case#),2)
as I've seen in other languages, but how would I implement this in Simple mode, assuming it was the ticket for my issue?
Appreciate any assistance/guidance.
The same question
Hi PS,
Thanks for reaching out. Depending on what data type your data is stored as, and which RDBMS you're using, you'd be seeing '.00' because that is how arithmetic for certain data types are treated in SQL. As such, it is likely the case that your 'Eff_DTC2' data itself is causing this.
I can run this exact query in the Simple Builder and it's correct:
This is being executed in MySQL with the data type being:
You can't do something like "DEC (SUM (Eff_DTC2),2) / DEC (COUNT (Case#),2)" in the Simple builder, as you can't use db-specific functions there, and you can't do this in the Freehand SQL builder either, as you can't utilize aggregations (SUM, COUNT) there. You may wind up either needing to CAST the "Eff_DTC2" value to something else, then apply an aggregation to the field, or change the data type at the db-level.
Can you check in your database itself to see what data type this field is stored as and let me know both which data type and which RDBMS you're using?
Regards,
Mike
Hi PS,
Thanks for reaching out. Depending on what data type your data is stored as, and which RDBMS you're using, you'd be seeing '.00' because that is how arithmetic for certain data types are treated in SQL. As such, it is likely the case that your 'Eff_DTC2' data itself is causing this.
I can run this exact query in the Simple Builder and it's correct:
This is being executed in MySQL with the data type being:
You can't do something like "DEC (SUM (Eff_DTC2),2) / DEC (COUNT (Case#),2)" in the Simple builder, as you can't use db-specific functions there, and you can't do this in the Freehand SQL builder either, as you can't utilize aggregations (SUM, COUNT) there. You may wind up either needing to CAST the "Eff_DTC2" value to something else, then apply an aggregation to the field, or change the data type at the db-level.
Can you check in your database itself to see what data type this field is stored as and let me know both which data type and which RDBMS you're using?
Regards,
Mike
Thanks, Mike.
I pulled out an old trick and gave it a shot: I multiplied and divided the formula by 100:
100.00 * SUM( Eff_DTC2 ) / COUNT ( Case# ) / 100.00
Now the output is correct to two decimals. Maybe don't need the two decimals for the 100, but as long as it works...
Thanks, Mike.
I pulled out an old trick and gave it a shot: I multiplied and divided the formula by 100:
100.00 * SUM( Eff_DTC2 ) / COUNT ( Case# ) / 100.00
Now the output is correct to two decimals. Maybe don't need the two decimals for the 100, but as long as it works...
Hi PS,
Good to hear! This does indeed work in certain RDBMS'. Considered we appear to be squared away here I'll go ahead and close this case out for now then, but please don't hesitate to reach back out with any additional questions or concerns.
Regards,
Mike
Hi PS,
Good to hear! This does indeed work in certain RDBMS'. Considered we appear to be squared away here I'll go ahead and close this case out for now then, but please don't hesitate to reach back out with any additional questions or concerns.
Regards,
Mike
Replies have been locked on this page!