# Parameters and if statement statement calculations with aggregates help needed

**brady.holzhauer**Jun 6, 2018 7:52 AM

I have created a report that has the following features:

- Multiple parameters
- A "start date" date parameter
**I need to know how to make this always update to the most recent date!**

- A "compare by" parameter
- This lets you choose a "current period" to evaluate (day, month, quarter, year)

- A "compare to" paramater
- This lets you choose a "comparison period" to evaluate (previous period, last year)

- A "display" parameter
- This lets you choose a dimension (categories of individual rows, etc.)

- A "
**Metric**" parameter**This one is entirely broken, and the main problem I need to solve!**

- A "start date" date parameter
- Specific measures for these parameters
- Current Period value
- Comparison Period value
- Difference Value
- % Difference Value

I want to do three different **metrics** that you can choose from. These are the three:

- Volume (this is based on the count of individual items in the data)
- Sales Dollars (this is based on the sum of the sale price column)
- Net Recovery (this is a % formula)

Right now, I am setting up the metric parameter to work off of a "select a metric" measure.

That select a metric measure looks like this:

IIF([Metric]="volume",[item],0)

As you can see, there are no aggregates in that formula. It also ONLY does volume, one of the three I need to switch between. I got the volume to work by itself only by NOT aggregating it, and making sure the default aggregation of the "item" measure was "count".

However, when I try to add the other metrics:

IIF([Metric]="volume",[item],IIF([Metric]="Sales",[Sale Price],IIF([Metric]="Net Recovery",[Net Recovery %],0)))

**It tells me I cannot mix aggregate and non aggregate arguments with this function.**

I tried this too:

IIF([Metric]="volume",count([item]),IIF([Metric]="Sales",sum([Sale Price]),IIF([Metric]="Net Recovery",[Net Recovery %],0)))

The good news is that this works.

However, upstream, it does not. I have calculations for Current and Comparison Periods, and this breaks them.

IF

(DATETRUNC([Compare by], [Closed Date]) =

DATETRUNC([Compare by], [Start Date])

AND

DATETRUNC('day', [Closed Date]) <=

DATETRUNC('day', [Start Date]))

THEN

[Select a metric]

END

**The error comes back to "cannot mix aggregate and non aggregate arguments or results in "if functions"**

**I thought maybe I needed to create a way to show net recovery as an aggregate so I created it as a calculation in that original select a metric calculation.**

**IIF([Metric]="volume",COUNT([item]),IIF([Metric]="Sales",SUM([Sale Price]),IIF([Metric]="Net Recovery",([Net Dollars])/SUM([original value])*100,0)))**

This is valid, but it did not fix the issue with the current and comparison period calculations. Can anyone help?