Parameters and if statement statement calculations with aggregates help needed
brady.holzhauer Jun 6, 2018 7:52 AMI 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?