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!
• 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:

1. Volume (this is based on the count of individual items in the data)
2. Sales Dollars (this is based on the sum of the sale price column)
3. 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?