9 Replies Latest reply on Jun 8, 2018 9:32 AM by Andrew Bickert

# Parameters and if statement statement calculations with aggregates help needed

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?

• ###### 1. Re: Parameters and if statement statement calculations with aggregates help needed

Hi Brady,

Are you able to attach a workbook by chance (even if it is with sample data) so we can get a better understanding of the data structure?

Andrew

• ###### 2. Re: Parameters and if statement statement calculations with aggregates help needed

I will upload shortly!

• ###### 3. Re: Parameters and if statement statement calculations with aggregates help needed

I am not sure I can share it because it's an encrypted data source.

I have attached pictures below:

This error only exists because those two measures aren't valid at the moment.

• ###### 4. Re: Parameters and if statement statement calculations with aggregates help needed

better picture of select a metric:

• ###### 5. Re: Parameters and if statement statement calculations with aggregates help needed

Hi Brady,

A couple things that I noticed when reviewing the screenshots.

1. For your [Compare By] paramenter, do you have the values set to lowercase (ie: day, month, year). I can see the parameter control in the top right but can't tell if the value is set to lowercase and the display is set to uppercase. This would effect your current period calculation as the DateTrunc function requires the lowercase version of the Compare By.

2. Net Dollars: Is this already an aggregated calculation by chance? In your [Select a Metric] calculation you have ([Net Dollars])/[Sum([MR...]) and the calculation shows as correct which makes me think that it is already aggregated, if it is not then you would want to wrap a sum or attr around it.

3. With a parameter date there is not necessarily a good way that I know of to default it to todays date. I have in the past set the default value to 1/1/2018 (or similar) and changed the name to include (*Default is 1/1/2018) then build a calculation that checks if it is the default date otherwise chooses the selected date.

Parameter Control:

Andrew

• ###### 6. Re: Parameters and if statement statement calculations with aggregates help needed

Sorry for the delay!

Please see my notes:

1. For your [Compare By] paramenter, do you have the values set to lowercase (ie: day, month, year). I can see the parameter control in the top right but can't tell if the value is set to lowercase and the display is set to uppercase. This would effect your current period calculation as the DateTrunc function requires the lowercase version of the Compare By.

It is set up as lower case with only the display as uppercase.

2. Net Dollars: Is this already an aggregated calculation by chance? In your [Select a Metric] calculation you have ([Net Dollars])/[Sum([MR...]) and the calculation shows as correct which makes me think that it is already aggregated, if it is not then you would want to wrap a sum or attr around it.

Net dollar is already aggregated yes. It is basically "sale price - fees" from a logic standpoint. The MR... is the original value I referenced before.

3. With a parameter date there is not necessarily a good way that I know of to default it to todays date. I have in the past set the default value to 1/1/2018 (or similar) and changed the name to include (*Default is 1/1/2018) then build a calculation that checks if it is the default date otherwise chooses the selected date.

This worked for the date parameter. Thank you!

• ###### 7. Re: Parameters and if statement statement calculations with aggregates help needed

Hi Brady,

You may be able to create calculated fields for each of your components in Select A Metric: and then do a calculation for Select A Metric like this:

If I am understanding your need correctly for your comparison period you need an Identifier more or less which could be this:

and then apply your Test to find the Select A Metric:

Which may allow your workbook to be able to choose base off the parameter [Metric] and show this:

Andrew

• ###### 8. Re: Parameters and if statement statement calculations with aggregates help needed

This still caused errors on the current period, the difference, and % difference fields, so nothing would display

• ###### 9. Re: Parameters and if statement statement calculations with aggregates help needed

Hmmm, are you able to produce a sample data set so we can see the structure of your data and calculations better?

Andrew