# cannot mix aggregate and non-aggregate comparisons or results in 'if' expression

**Alex Braga**Nov 8, 2016 1:36 PM

Hi all,

I'm trying to calculate some metrics for a particular time period (say 2015 and 2016). This is a healthcare exercise, and the variable I'm calculating is called "Procedures per Claim". For this variable I want to calculate and compare:

**[1.PROCS_CLAIM_Previous]: **Procedures per Claim at a point in time (let's use 3 months for this example) in 2015

**[2.PROCS_CLAIM_Current]**: Procedures per Claim at same point in time (same 3 months) in 2016

**[3.PROCS_CLAIM_Diff]: **The difference in [Procs_per_Claim] for the time periods

**[4.PROCS_CLAIM_Percent_Diff]: **The % difference in [Procs_per_Claim] for the time periods

In order to do this I have Filters, Parameters and Variables defined as follows:

**[Procs_per_Claim]: **measure. Defined as:

SUM([Proc Count])/COUNTD([CLAIMID])

Where: [Proc Count] = count of procedures, [CLAIMID] = individual claim numbers

**[Current or Previous]: **Dimension. Defines the time periods to be evaluated, IF Statement as follows:

//If the period is LM or YTD, then if the year of the order date is the same as the year of the

//selected month,it's the current period, otherwise it's the previous period

if [Period] <3 then IIF(year([SVCBEGDATE]) = year([SVCBEGDATE (MONTHS) PARAMETER]),1,0)

//If the period is L3M, then if the difference in months is less than 3 it's the current period,

//otherwise it's the previous period

elseif [Period]=3 then IIF( [Difference in Months] <3 , 1, 0)

//If the period is L12M, then if the difference in months is less than 12 it's the current period,

//otherwise it's the previous period

else IIF( [Difference in Months] < 12, 1, 0)

end

**[Difference in Months]: **Measure. Shows the number of months from Service Begin Date [SVCBEGDATE] to a number of months specified by the parameter [SVCBEGDATE (MONTHS) PARAMETER]

DATEDIFF('month', [SVCBEGDATE], [SVCBEGDATE (MONTHS) PARAMETER])

**[1.PROCS_CLAIM_Previous]: **Measure. Should be the [Procs_per_Claim] records for the previous period (2015)

if ATTR([Current or Previous]) = 0 then [Procs_per_Claim] end

**[2.PROCS_CLAIM_Current]: **Measure. Should be the [Procs_per_Claim] records for the current period (2016)

if ATTR([Current or Previous]) = 1 then [Procs_per_Claim] end

**[3.PROCS_CLAIM_Diff]: **Measure.** **Should be the difference in [Procs_per_Claim] for the two time periods

[2.PROCS_CLAIM_Current]-[1.PROCS_CLAIM_Previous]

**[4.PROCS_CLAIM_Percent_Diff]: **Measure. Should be the % difference in [Procs_per_Claim] for the two time periods

([2.PROCS_CLAIM_Current] - [1.PROCS_CLAIM_Previous]) / [1.PROCS_CLAIM_Previous]

****************************************************

RESULTS (OR LACK THEREOF)

As I drag **[1.PROCS_CLAIM_Previous] **and** [2.PROCS_CLAIM_Current]) **to the rows shelf I get no results. The formulas for the difference and percent difference are also wrong and not returning any values.

I am running into aggregation errors, and I'm not sure what I am missing.

By the way, the code above is working just fine for a **[PAID] **measure. But when substituting **[PAID] **for **[Procs_per_Claim] **the calculations fall apart due to aggregate errors.

Your help and and expertise would be highly appreciated. What I am missing?

Thanks!