3 Replies Latest reply on Oct 24, 2016 2:05 PM by Chris Hogan

# Beginner Question: Filter Based on MAX(Sum(*Measure*))

I have a dashboard that shows total sales over time by salesperson ID.  I have a date in the column shelf and an aggregate (SUM of sales) in the rows shelf.  I have a date filter to limit the results to the previous X days.

I want to show only those salespeople who have at least one day in the date range with sum(sales) greater than a threshold parameter called "Sales Threshold."

Right now I have a filter that limits ID by MAX([sales])>=[Sales Threshold].  I believe this is limiting the result to members who have had one or more individual sales greater than the threshold parameter.  How do I:

1. Limit the results to only IDs with a day's total sales surpassing the threshold

2. Limit the threshold test to the date range of interest

I have searched for other people asking the same question.  I think the solution involves LOD expressions and/or Window_Max but I have been unable to make either solutions work.

Has anyone dealt with a similar issue?

Thanks,

Chris

• ###### 1. Re: Beginner Question: Filter Based on MAX(Sum(*Measure*))

I think this is what you're going for. Here's what I did:

1. Right-click the ID filter and select Remove.

2. Right-click the Date filter and select Add to Context.

3. Create a new calculated field (I called it "Filter") with the following formula:

IF {FIXED [ID] : MAX({FIXED [ID], [Date] : SUM([Sales])})}>=[Sales Threshold]

THEN "YES"

ELSE "NO"

END

4. Drag Filter to Filters and select YES then click OK.

1 of 1 people found this helpful
• ###### 2. Re: Beginner Question: Filter Based on MAX(Sum(*Measure*))

Hi Chris,

check with following calculation.

Attaching screen shot.

1. 1st create view with ID, Date, sales.

2. next create calculated field [Max Date] as { MAX(([Date])) }

3.  next create another cal field calculation1 as [Date]<=[Max date]-60 AND [Sales]>=[Sales Threshold] and drag it into filter shelf and select true.

Find below screen shot.

Let me know its working or not.

Regards,

venkat.

1 of 1 people found this helpful
• ###### 3. Re: Beginner Question: Filter Based on MAX(Sum(*Measure*))

Thanks a lot for your help.  This worked for me.  It did not occur to me to use an LOD expression within an LOD expression.

Chris