4 Replies Latest reply: May 24, 2018 3:11 PM by Elizabeth Viso RSS

    Data Incorrect When not Filtered

    Elizabeth Viso

      Hello,

       

      I am summing up the about of labor hours for my dataset and when it is filtered the amount is correct but when it's not filtered the amount is being doubled and I can't figure out why this is happening.

       

      My expression is:

       

      =Sum(Aggr(Sum([LABOR HRS / YEAR]),[Record ID]))

      /

      Count(DISTINCT Facility)

       

      I've tried other ways and nothing is working.

       

      This is what my data looks like:

       

      Capture11.PNG

       

      And this is the script:

       

      TEMP:

      LOAD "_ROWNUM_" AS [Record ID],

          "System 1",

          STANDARD,

          "ITM TYPE",

          "TYPE OF ACTIVITY",

          FREQUENCY,

          "NFPA                              CODE / STANDARD",

          "INVENTORY QUANTITY",

          "FREQ NUM",

          "ITM ACTIVITIES",

          "LABOR HRS / YEAR",

          "UHealth Tower",

          "UMHC SCCC",

          "UMHC BPEI",

          "UMHC LFMC",

          "UMHC Deerfield",

          "UMHC Plantation",

          "UMHC Kendall",

          PAC,

          "UMHC Applebaum",

          CEP,

          "UMHC Ear Institute",

          "UMHC Pain Clinic",

          "UMHC Marlin Park",

          "UMHC Coral Springs",

          "UMHC Hollywood",

          PSHY,

          "UMHC Radiology";

      SQL SELECT "_ROWNUM_",

          "System 1",

          STANDARD,

          "ITM TYPE",

          "TYPE OF ACTIVITY",

          FREQUENCY,

          "NFPA                              CODE / STANDARD",

          "INVENTORY QUANTITY",

          "FREQ NUM",

          "ITM ACTIVITIES",

          "LABOR HRS / YEAR",

          "UHealth Tower",

          "UMHC SCCC",

          "UMHC BPEI",

          "UMHC LFMC",

          "UMHC Deerfield",

          "UMHC Plantation",

          "UMHC Kendall",

          PAC,

          "UMHC Applebaum",

          CEP,

          "UMHC Ear Institute",

          "UMHC Pain Clinic",

          "UMHC Marlin Park",

          "UMHC Coral Springs",

          "UMHC Hollywood",

          PSHY,

          "UMHC Radiology"

      FROM "2018 UMHC System Inventory Count__8643507062630276s_(Home)";

       

       

      TEMP2:

      Crosstable ([Facility Temp], [Count Temp],11)

      LOAD

      [Record ID],

          "System 1",

          STANDARD,

          "ITM TYPE",

          "TYPE OF ACTIVITY",

          FREQUENCY,

          "NFPA                              CODE / STANDARD",

          "INVENTORY QUANTITY",

          "FREQ NUM",

          "ITM ACTIVITIES",

          "LABOR HRS / YEAR",

          "UHealth Tower",

          "UMHC SCCC",

          "UMHC BPEI",

          "UMHC LFMC",

          "UMHC Deerfield",

          "UMHC Plantation",

          "UMHC Kendall",

          "PAC",

          "UMHC Applebaum",

          "CEP",

          "UMHC Ear Institute",

          "UMHC Pain Clinic",

          "UMHC Marlin Park",

          "UMHC Coral Springs",

          "UMHC Hollywood",

          "PSHY",

          "UMHC Radiology"

      RESIDENT TEMP;

       

       

      Drop Table TEMP;

      [ALL DATA]:

      LOAD RecNo() as ID,

      Replace([Facility Temp],'UMHC ','') as Facility,

           If([Count Temp]=0,'N/A',[Count Temp]) as InventoryCount,

        *

      Resident TEMP2;

       

       

      Drop Table TEMP2;

       

       

      Drop Fields [Facility Temp], [Count Temp];

       

       

       

      Any ideas?