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

[Anonymous]

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,

[sunny_talwar]

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)

[vishsaggi]

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?

[sunny_talwar]

Ya, I think this might be better

[Anonymous]

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 

[sunny_talwar]

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, vishsaggi‌'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

[vishsaggi]

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

[vishsaggi · Accepted solution]

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

[sunny_talwar]

Right yet again

[vishsaggi]

Thanks Sunny, couple of things learnt today. 🙂 Cheers..