How to output with a control variable inside cfquery name?

[bonneylake]

Hey Acoder,

Around which cost? the one in isnumeric, the one where cost neq 0.0, the cost's in the pricediff? an then should i still be outputting using #pricediff#?

Thank you,
Rach

[bonneylake]

Hey Acoder,

I tried the following, but it still says all of the costs are n/a an that don't make sinse based on the math, especially for record 292 because when i do that one i get a value of 0.04.

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost) and #val(cost)# neq 0.0>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03 and pricediff lt -0.03>
#pricediff#
<cfelse>
N/A
</cfif>
</cfif>

I also tried the following (took out "and pricediff lt 0.03") an that shows all the prices above 0.03, an puts N/A for anything under 0.031.so would this work or not work?

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost) and #val(cost)# neq 0.0>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03  >
#pricediff#
<cfelse>
N/A
</cfif>
</cfif>

Also, i noticed something. I thought i was suppose to get less than +- 3% but i relooked at it an i am suppose to get >(greater than) + - 3% so will what we have still work? or is what we have only working to get less than?

Thank you,
Rach

[acoder]

It's a bit of a duh! moment. The line:

<cfif pricediff gt 0.03 and pricediff lt -0.03>

will always be false because they are mutually exclusive. It needs to be or:

<cfif pricediff gt 0.03 or pricediff lt -0.03>

[bonneylake]

Hey Acoder,

Ok so i changed it to an or and now if a number if its greater than 3% it will show 0.04. An anything below 0.03 it shows n/a is this correct?

there is a few parts i am confused on. One value has - 0.39 and the other is 0.03 (one negative one positive). If you could explain what that means for one to be negative and one to be positive it would be helpful because it has me a bit off

another part i am confused on is where does the + 3% part show up?would that just be 0.03?

An the last question i have is there anyway to make the number smaller? right now i get 0.0489140480591 , is there anyway to shorten that to just 0.04 (don't think they need to see the other numbers).I would do dollarfomart around it but not sure if it should be a price of not, i am guessing it should since its a price difference but thought i would ask your judgment on that one.

Thank you :),
Rach

[acoder]

To round the number, use Round() and multiply the number by 100 to get the percentage, so 0.03 would become 3.

And yes, the +3 would be 0.03, but if you want to show a plus sign, you can add a cfif > 0 check to show +.

[bonneylake]

Hey Acoder,

Do you think they would want to see just the rounded number or see 0.03? They say % price different so i am thinking the 0.03, but i don't know how i just display the 0.03.

An also wanted to make sure, the if one value is -0.04 then it means its lower then 3 percent right?

An the last question i am a bit confused on how to show the + would i just do <cfif
pricediff gt 0.03 > + >#pricediff#</cfif>. Little bit confused on it.

Thank you,
Rach

[acoder]

I'd think as a percentage, so you'd use round to round it to two decimal places, e.g. 0.03245678 would become 3.25% by multiplying by a 100 and then rounding. -0.04 is less than -3%.

Since you want to show the +, you could separate the two price differences, e.g.

<cfif pricediff gt 0.03>+#pricediff#
<cfelseif pricediff lt -0.03>#pricediff#
<cfelse>N/A
</cfif>

[bonneylake]

Hey Acoder,

So is this the correct way to do the round? because mine didn't display like 3.25% it instead just kept putting first number 0. This is how i did it.

#Round(pricediff * 100)#

Thank you,
Rach

[acoder]

To round to 2 decimal places, you'd multiply by 100 and then divide by 100, but since you want the decimal (0.03) as an integer too, you'd multiply by another 100, so something like:

<cfset pricediff = round(pricediff * 10000)/100>

[bonneylake]

Hey Acoder,

My last question (i hope is my last) is instead of displaying the round as your example how would i display it as .039 instead of how it originally was which was 0.394784718011 ? would something like #decimalFormat( Pricediff)# work?

here is what i got in total code wise now.

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost) and #val(cost)# neq 0.0>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfset pricediff = round(pricediff * 10000)/100>
<cfif pricediff gt 0.03>
+#Pricediff#
<cfelseif pricediff lt -0.03>#pricediff#
<cfelse>
N/A
</cfif>
</cfif>

Thank you,
Rach

[bonneylake]

Hey Acoder,

Asked boss and he said do it as .039 instead of the other idea we had. So would
#DecimalFormat( Pricediff)# work? when i tried it that way (decimal format) i got -.39 and i was excepting a 0 in front of the 3 so just wondering if this would be correct way to do it or not?

Thank you,
Rach

[acoder]

Well, if it's 0.39xxxxx (a whole lot of numbers), then you can't change it to 0.039. I assume you meant 0.39.

Decimalformat would work except that it won't round, so

<cfset pricediff = round(pricediff * 100)/100>

might be a better bet.

PS. it is rare to have such a huge price difference, so maybe you should be testing with something more "average".

[bonneylake]

Hey Acoder,


Well when i asked boss if he wanted it to be like 0.39 he wrote me back saying .039 should be fine so i guess either a) he put the decimal in wrong place or b) i got confused and he wants it as 0.39.An yeah it is high numbers i agree. Not sure why they didn't just type them in there rounded.but your suggestion worked perfectly. Thank you so much for all the help you have no idea how much i appreciate it :)

Thank you,Thank you :),
Rach

[acoder]

I'm glad it did. So is that the thread finished?

[bonneylake]

Hey Acoder,

Almost i got one more question i want to ask in case the people i made the report want this changed. How would i change the part where -1 appears when all 3 prices don't have a price for a particular part?i tried to change it but i am not even sure where the -1 comes from. i know its from the code below but not sure how it appears.Thought 0 would appear if none of them had a price.

 <cfset lowest = 0>
 <cfif techdata.recordcount neq 0 and techdata.COST neq 0.00> 
     <cfset lowest = techdata.COST>
      </cfif>
    <cfif synnx.recordcount neq 0 and synnx.Unit_Cost__wo_PromoRebate neq 0.00> 
      <cfif lowest eq 0>
        <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
      <cfelseif lowest gt synnx.Unit_Cost__wo_PromoRebate>
        <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
     </cfif>
   </cfif>
 <cfif dh.recordcount neq 0 and dh.unit_cost neq 0.00> 
      <cfif lowest eq 0>
        <cfset lowest = dh.unit_cost> 
      <cfelseif lowest gt dh.unit_cost>
        <cfset lowest = dh.unit_cost> 
     </cfif>
   </cfif>

Thank you :),
Rach