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,

[Anonymous]

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!

[vishsaggi]

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

[#4]

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

[vishsaggi]

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.

[Anonymous]

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.

[Anonymous]

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.