Data Incorrect When not Filtered

[hammermill21]

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:

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?

[stevedark · Accepted solution]

Hi,

I can't see why you would be using the AGGR function in your expression, you may find it works better without it.

=Sum([LABOR HRS / YEAR]) / Count(DISTINCT Facility)

The doubling up may be simpler than that though, in the screengrab there appears to be a totals row, are you bringing that in also?

If it is that, add a where statement to remove it;

RESIDENT TEMP

WHERE ([ITM TYPE] <> '' AND (NOT IsNull([ITM TYPE]))

;

Hope that helps,

Steve

[hammermill21]

Hey Steve,

That works now, I had tried that before and nothing. I may have just needed to refresh my app.

Thank you!

[stevedark]

Hi,

Glad that worked out.  You always need to do a reload after any script change, so that could well have been the issue.

[hammermill21]

Noted

Thanks again!