15 Replies Latest reply: Mar 13, 2017 4:21 PM by Mike Czerwonky

# Find the Min Date for the Max Sales in a Summary Text Box

Please find attached my sample QVW.   I am trying to solve a double AGGR issue and need some expert assistance.  Here is a picture of my dashboard screen.

My business user has a requirement to have summary boxes above the straight table.   In this example, we want to know the Max Sales and then answer the next 3 questions.  In the left summary text object, you can see the formula for AGGR the max sales by date.   This is fine for finding the max.  What I really want is to identify the minimum interval dimension and assign it to a variable. so that I can answer the other questions using set analysis.  The logic used to solve this will be used 10 more times to solve this issue with different data sets and calculations.

Greetings,

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

Try these three expressions

1) =FirstSortedValue(Interval, -aggr(sum(Sales)-Date/1E10,Date))

2) =Only({<Interval = {"\$(=FirstSortedValue(Interval, -aggr(sum(Sales)-Date/1E10,Date)))"}>} Salesman)

3) =Sum({<Interval = {"\$(=FirstSortedValue(Interval, -aggr(sum(Sales)-Date/1E10,Date)))"}>} Commission)

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

Sunny,

Thanks again for your quick response.  Hope you are going to make it to Qonnections in Orlando this year, I would really like to meet you.

Can you please explain the /1E10 portion of the expressions above?

This solution sure will be more efficient that building variables.

Thanks,

Mike

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

Well the idea is to arrange the Interval by a order in which I can pick the highest or lowest value of interval

Sum(Sales) - Date/1E10 (where 1E10 is nothing but 10,000,000,000)

1000 - DATE/10,000,000 will be a number slighly smaller than 1000. From this list, I am picking the highest number. Having said that, Vishwarath's proposed solution seems to be much better and less complicated. So, I would go with his way of doing this.

P.S. I am going to the conference this year. Def. look forward to seeing you there.

Best,

Sunny

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

Thanks Sunny for the explanation, This is interesting, never would have thought like that. :-)

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

Sunny I just used your expressions in those 3 text boxes like below and it gives the same result as yours. Might not need the additional Only() or Sum() functions here. Whats your opinion? Correct me...

= FirstSortedValue(Date, -Aggr(Sum(Sales) - Date/1E10, Date))             -> 1/6/2017

= FirstSortedValue(Salesman, -Aggr(Sum(Sales) - Date/1E10, Date))     -> Larry

= FirstSortedValue(Commission, -Aggr(Sum(Sales) - Date/1E10, Date)) -> 250

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

Right yet again

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

Thanks Sunny, couple of things learnt today. :-) Cheers..

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

Outstanding effort so far.  One more question and I will be ready to close this thread.  Assume that I have another column in my straight table that is an expression that I want the result of the expression for the min interval to appear in a summary box.   Does firstsortedvalue only work with dimensions or can I put an expression where the dimension Salesman and Commission are in your solutions for Expr 2 and 3?

In my original request, I was going to use this solution to assign my minimum interval to a variable and then reference that variable in set analysis in my summary box for the expression.

I do appreciate all of you help!

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

Quite did not get you. You want result of that expression from your straight table? Can you give us an example?

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

Vishwarath,

I have my problem resolved and learned two very helpful lessons today.  Thanks for your efforts along with the Master, I am moving forward.

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

Check this?

Expr1: = Min({< Sales = {\$(= Max(Aggr(Sum(Sales), Date))) } >} Date)

Expr2: = Only({< Date = {'\$(= Min({< Sales = {\$(= Max(Aggr(Sum(Sales), Date)))}>} Date))' } >} Salesman)

Expr3: = Min({< Date = {'\$(= Min({< Sales = {\$(= Max(Aggr(Sum(Sales), Date)))}>} Date))' } >} Commission)

stalwar1 Just wondering can we write this way as well? I am getting the results but have a slight doubt in my brains that I am not sure.

And can you explain what you mean by Aggr(Sum(Sales) - Date/IE10, Date) in your expr?

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

Ya, I think this might be better

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

Don't mean to be more than exceptionally stupid, but what is the "Interval" field in this table? Why do you need it?

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

Interval is the field that holds numeric part of the field Date. If you do Floor(Date) you will have the numeric part of it.

• ###### Re: Find the Min Date for the Max Sales in a Summary Text Box

It is just a numeric representation of the date.  Easier to add to a number and get max or min.  it is arbitrary in that you could use any dimension, thought it might be faster with a number rather than text.  In my app, I also have 15 min increments of time and I use a row number field for a unique value assigned to a time interval.  This exercise is to solve the concept.  My date field is actually YYYYMMDDTTTT in 15 min increments.