1 Reply Latest reply on Aug 6, 2018 10:08 PM by patrick.byrne.0

    Help Running Query and Calculation

    Kimberly Lacher

      Hello, i am a summer intern that has an engineering background but little background in tableau. I am trying to create a production data table but i am having trouble inputting the calculation and query sent to me by our IT guy. I would work with him but hes out of the office and i need help today.

      So my instructions are here

      Given below query calculates Barrel, CurrentDateLastValue and LastDayLastValue, which we use in our formula to calculate Totals and Running Total, we just change the dates to get CurrentDateLastValue and LastDayLastValue

       

      Select case tblequiptank.capacity

      when 90 then ifnull(((tbloilproduction.inches + (tbloilproduction.feet * 12 )) * .75),0)

      when '100-10 ft' then ifnull(((tbloilproduction.inches + (tbloilproduction.feet * 12 )) * 0.83),0)

      when '100-8 ft' then ifnull(((tbloilproduction.inches + (tbloilproduction.feet * 12 )) * 1.04),0)

      when 210 then ifnull(((tbloilproduction.inches + (tbloilproduction.feet * 12 )) * 1.16),0)

      when 200 then ifnull(((tbloilproduction.inches + (tbloilproduction.feet * 12 )) * 1.67),0)

      when 300 then ifnull(((tbloilproduction.inches + (tbloilproduction.feet * 12 )) * 1.67),0)

      when 250 then ifnull(((tbloilproduction.inches + (tbloilproduction.feet * 12 )) * 1.67),0)

      when 400 then ifnull(((tbloilproduction.inches + (tbloilproduction.feet * 12 )) * 1.67),0)

      when 500 then ifnull(((tbloilproduction.inches + (tbloilproduction.feet * 12 )) * 2.78),0)

      else '0' end from tbloilproduction inner join tblequiptank on tbloilproduction.equipmentid = tblequiptank.equipmentid

      where tbloilproduction.equipmentid = ?EquipmentID and productiondate =2018-06-06"';

       

      TodaysAdjustments: Calculated by sum of Volumes and we exclude Reason which is Water removed from tbladjustments Table.

       

      Query is something like this (Dim TodaysAdjustment As Double = Select ifnull(sum(ifnull(Volume,0)),0) from tbladjustments where reason <> 'Water Removed' and date(adjdate) = 2018-06-06 and EquipmentID=?EquipmentID

       

      Totals =Totals + ((CurrentDateLastvalue - LastDayLastvalue) + TodaysAdjustment);

       

      each time when we run query we pass equipmentid and dates one by one for whole month

       

      Running Total = Running Total + Total

       

       

      So i need help finding out where and how to enter this. Is it ran in a Calculated Field or a Custom SQL? I am using the MySQL 5.3 ODBC.