8 Replies Latest reply on Nov 8, 2017 1:41 PM by Jim Dehner

# Aggregate vs Non-Aggregate in If statement with a Parameter

I have the following calculation and i'm getting the "cannot mix aggregate and non-aggregate comparisons or results in "If" expressions".

The [Reporting Period] reference is a parameter. The [CandidateCNT] is an aggregation.

IF [Start Date HR] > [Reporting Period]

AND [HR Status] = 'Offer Accepted'

OR [HR Status] = 'Initiate Background Check'

OR [HR Status] = 'Background Check Pending'

OR [HR Status] = 'Review References'

OR [HR Status] = 'Recruiter Escalation'

OR [HR Status] = 'ERS Escalation'

OR [HR Status] = 'Mobility Escalation'

OR [HR Status] = 'Validate Documentation'

OR [HR Status] = 'Hire Recruiter Form'

OR [HR Status] = 'Forms Review'

OR [HR Status] = 'Hired'

THEN Sum([Candidates Hired])/[CandidateCNT] END

What am I missing and why? I'm still trying to wrap my head around LOD expressions.

• ###### 1. Re: Aggregate vs Non-Aggregate in If statement with a Parameter

Hi

The issue with aggregates and non-aggregates is not limited to LOD expressions -

if ANY of you measures or dimensions is aggregated then all the measures or dimensions need to have an aggregate

In your case each of your "string - dimension" need to me wrapped in an aggregation function like Min(), Max() or Attr() -

The same will be true for your Starting Date and Reporting Period

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Aggregate vs Non-Aggregate in If statement with a Parameter

As Jim mentioned above, you have to wrap the non aggregated  fields with functions or if it is a dimension  then you can use ATTR().

• ###### 3. Re: Aggregate vs Non-Aggregate in If statement with a Parameter

Thanks, guys.  That's what I tried to do and i'm still getting the same error. Here's my revised calculation:

IF ATTR([Start Date HR]) > ATTR([Reporting Period])

AND ATTR([HR Status])= 'Offer Accepted'

OR ATTR([HR Status]) = 'Initiate Background Check'

OR ATTR([HR Status]) = 'Background Check Pending'

OR ATTR([HR Status]) = 'Review References'

OR ATTR([HR Status]) = 'Recruiter Escalation'

OR ATTR([HR Status]) = 'ERS Escalation'

OR ATTR([HR Status]) = 'Mobility Escalation'

OR ATTR([HR Status]) = 'Validate Documentation'

OR ATTR([HR Status]) = 'Hire Recruiter Form'

OR ATTR([HR Status]) = 'Forms Review'

OR ATTR([HR Status]) = 'Hired'

THEN{FIXED [Req ID]: sum([Candidates Hired])/[CandidateCNT]}END

I wrapped the sum with a Fixed LOD to make sure the subtotals aggregate correctly at the Req_ID level.

• ###### 4. Re: Aggregate vs Non-Aggregate in If statement with a Parameter

Wrap you Fixed function in an aggregating function like min or max

• ###### 5. Re: Aggregate vs Non-Aggregate in If statement with a Parameter

Closer but not quite there yet. .  I've attached a sample workbook. Not all the subtotals are showing and the grand total at the bottom is blank as well.

Out of curiosity, why would I use a min or max around the FIXED function?

• ###### 6. Re: Aggregate vs Non-Aggregate in If statement with a Parameter

Hi

I'm not certain that I understand the data

but  that said lets get your viz correct

The Candidate Hired total/subtotal can be changed to use the average vs sum

See below - right click on the pill in the Measure value table and then select Total using- then Average

I don't understand your incoming hire totally 7 but the total there is drive by the formula used to calculate it

LOD  calculations are done at a high level in the order of operations and they create a an "aggregated dimension" - for fixed - tableau creates permutations of the combinations of dimensions that precede the colon (:) and then aggregates them by what follows the colon - the values are "fixed" at the level specified but are separate values that you can use in the viz or other calculated fields - when you being them into the viz or calculation you need to determine how you want to aggregate the permutations - e.g. if you are going to a more aggregate level than the permutation you would sum() -

Now my question - I don't understand the data - it looks like the 'hard' data has the "candidates hired' equal to the total number of candidates in each record for each record - is that really what you want?

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 7. Re: Aggregate vs Non-Aggregate in If statement with a Parameter

Yes, that's what I want because I only care about one of those records for each Req_ID. Each Req_ID can have multiple candidates so essentially i would be duplicating the Candidates Hired for each candidate when really i only care about one of them.

Does that make sense?

• ###### 8. Re: Aggregate vs Non-Aggregate in If statement with a Parameter

Understood - you know what you are doing

Did my response meet your need?

Jim