2 Replies Latest reply on Aug 14, 2018 5:25 AM by Stephen Groff

    LOD and mixing aggregates / non-aggregate etc

    Stephen Groff

      Hi All,


      First, let me get to the basics of this LOD


      I have 2 tables.  A Machines Completed Table. A Defect Table


      In the Machines Table I have a list of serial numbers


      In the Defects Table I have a column with the serial number, another with the cause area ID, and another with the Event #


      I need to figure out which serial numbers didn't have a defect for a particular case area.


      Question:  Which machines didn't have a defect in Cause Area "300"?


      I need this to be a stand alone calculation, not a table.  Every solution someone sends me is a table view, I need this to be a working measure that gives me a number.  Meaning I can drag the measure to the Viz and it gives me a number of machines without a defect.  Not a table of serial numbers, then a column with defects and then count the serials with 0 defects.  I have to be able to use this calculation with other measures (calcs)

      i.e.   MEASURE (# of Zero Defect Machines) / # of Machines Produced.


      My LOD is  {FIXED [Serial #]:COUNTD(IF [Cause Area ID]="300" THEN [Event #] END)}


      Then supplemental calc would be to count those serial numbers that didn't have an Event # tied to it.



      IF [LOD] = "0" then 1 END


      But I cannot mix aggregrate, etc.. etc.  I also sometimes get Oracle Error 00904, which I've circumvented by resetting the names of all my column headers from my custom SQL query.


      I've been trying to chip away at this for 3 weeks with no progress.  Can anyone help?