Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

hammermill21
Contributor II

Data Incorrect When not Filtered

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?

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Data Incorrect When not Filtered

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

4 Replies
Highlighted
MVP
MVP

Re: Data Incorrect When not Filtered

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
Contributor II

Re: Data Incorrect When not Filtered

Hey Steve,

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

Thank you!

MVP
MVP

Re: Data Incorrect When not Filtered

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
Contributor II

Re: Data Incorrect When not Filtered

Noted

Thanks again!