Jump to content


  • Content Count

  • Joined

  • Last visited

  • Days Won


TroyPurdue last won the day on June 9 2018

TroyPurdue had the most liked content!

About TroyPurdue

  • Rank
  1. Thanks for the reply. We ended up speaking with Caspio and it appears that we have to have them setup custom triggers and a shadow table in order to accomplish this task. So unfortunately it falls under custom development.
  2. Background: We have a large form used to score phone calls. Each field is recorded as a 1, 0 or blank depending on the answer. Each record also includes a Supervisor, Location, and Agent marker so as to build reports. We currently have tabular reports built at each level (Overall, By Supervisor, and By Agent) to show percentage scores for groups or metrics. Here is an example of an overall calculation: select (sum(IsNull(Greeting_Script,0))+sum(IsNull(Greeting_ID,0))+sum(IsNull(Greeting_Tone,0))+sum(IsNull(Greeting_Verify,0))+sum(IsNull(Greeting_Over_Verify,0))+sum(IsNull(Greeti
  3. Is there a way to move the bar chart labels to inside the bar instead of on top? Currently if the calculation is 100% then no label is visible unless I adjust the axis to go to 125% which is not the best solution in my opinion.
  4. I have a formula that is returning an Error in Formula because it is calculating all blank entries. I've tried to replace the blank with a '0' when it finds all empty values. The formula should return a 0% when the calculation finds all empty values. Here is what I have that is not working: select case when count(*) = 0 then 0 else count(*) end as (sum(IsNull(Accident_Injured,0))+sum(IsNull(Accident_InjuriesYes,0))+sum(IsNull(Accident_Damage,0))+sum(IsNull(Accident_Flipped,0)))/(count(Accident_Injured)+count(Accident_InjuriesYes)+count(Accident_Damage)+count(Accident_Flipped)) fr
  5. Thank you to everyone for your assistance. I have gotten the calculation work as expected. Here is my final formula: select (sum(IsNull(Listening_Listen,0))+sum(IsNull(Listening_Concerns,0))+sum(IsNull(Listening_Probe,0))+sum(IsNull(Listening_Paraphrase,0)))/(count(Listening_Listen)+count(Listening_Concerns)+count(Listening_Probe)+count(Listening_Paraphrase)) from RA_Received where Date>=N'[@BeginDate]' AND Date<=N'[@EndDate]' AND PreCall_SupervisorLocation='[@Location]' AND PreCall_Supervisor='[@Supervisor]' AND PreCall_AC='[@AC]' HOWEVER, when I try to graph my only option
  6. The WHERE function is working properly now. The IsNull is returning an error saying more than one argument is necessary. The formula returns no errors when IsNull is excluded.
  7. I agree that the final calculation being displayed for each record is not desired, but I do need to graph the final %. With the javascript solution I do not see how I can graph the value. I am still getting an error in the other formula because of the WHERE function. where Date>[@BeginDate] AND Date<[@EndDate] AND PreCall_SupervisorLocation=[@Location] AND PreCall_Supervisor=[@Supervisor] AND PreCall_AC=[@AC] Date, PreCall_SupervisorLocation, etc. are my table field names, and @BeginDate, @EndDate are my parameters passed from the search form. When I remove the WHERE func
  8. Here is what I have so far and it is returning Error in Formula select [@field:PreCall_Supervisor], [@field:PreCall_SupervisorLocation], [@field:PreCall_AC], [@field:Date], (sum([@field:Listening_Listen])+sum([@field:Listening_Concerns])+sum([@field:Listening_Probe])+sum([@field:Listening_Paraphrase]))/(count([@field:Listening_Listen])+count([@field:Listening_Concerns])+count([@field:Listening_Probe])+count([@field:Listening_Paraphrase])) from RA_Received where [@field:Date]>[@BeginDate] AND [@field:Date]<[@EndDate] AND [@field:PreCall_SupervisorLocation]=[@Location] AND [@fie
  9. I am trying this now... How do you add search criteria for a date range? Field @Date has Criteria1 and Criteria2 that are passing parameters from a search form.
  10. Example: Record 1 - Field1='1' Field2='1' Field3='BLANK' Field4='1' Record 2 - Field1='0' Field2='1' Field3='0' Field4='1' Record 3 - Field1='1' Field2='1' Field3='1' Field4='1' What I need to do is SUM all of the 4 fields and COUNT non-blanks for all four fields and then divide the SUM by the COUNT, and do this across Records. In this example, the way I need it to work would be that the SUM=9 and the COUNT=11 for total calculated average of 81.81%. In Caspio, I can create the two calculated fields to SUM and COUNT for each record. Then I can run an aggregation to SUM each ca
  11. Basically I'm wondering if there is an equivalent to AVERAGE([@field:Field1], [@field:Field2]) or a COUNT function so the formula could be ([@field:Field1]+[@field:Field2])/Count([@field:Field1], [@field:Field2]).
  12. What if some of the fields are blank? The dividing number is not static.
  13. I want to chart the average across multiple columns for ALL records in the table. I know I can create a calculated field to sum using [@field:Field1]+[@field:Field2] but I can't find a way to average them and then use that calculated field on the chart.
  • Create New...