All of the fields above/below are dimensions. I believe the issue is that the est_rev_nbr can be different for every estimate number. If we redo a budget, the est_rev_nbr will go up by 1. Some jobs have one estimate, and some have 4 or 5. Here is a better data sample (I am not sure how qvf's work, but can attach an excel spreadsheet if needed):
job_number estimate_number est_rev_nbr description amount
1000 307 0 Hotel 500
1000 307 1 Hotel 1000
1000 307 2 Hotel 1500
1000 307 0 Flight 200
1000 307 1 Flight 300
2000 311 0 Food 750
2000 311 1 Food 1100
2000 311 0 Flight 400
2000 311 1 Flight 300
3000 411 0 Flight 900
3000 411 0 Food 1750
There are 3 different jobs here. My desired output is (not in this format):
1000 Hotel: $1500
2000 Food: $1100
Flight: $300
Total(sum): $1400
3000 Flight: $900
Food: $1750
Total(sum): $2650
You will notice that the max(est_rev_nbr) is different, depending on the estimate/job_number. I only need the description amount from the max(est_rev_nbr), so you will see that the "Flight" actually drops off from job_number 1000 since Flight doesn't have an est_rev_nbr of 2. I believe the formula you gave me before, takes the highest est_rev_nbr across the whole data set, and only uses those (which is how I did originally ask the question). I am thinking I need one more aggr or comma in your original equation, so it calculates the firstsortedvalue (or lastsortedvalue in this case) for each estimate_number. I hope this all makes sense.
P.S. Someone else offered this for a possible fix, but it only works in certain cases also. In this example,
Quote_number = est_rev_nbr
Estimate = amount
= Sum({<Quote_number = {"= Max(Quote_number)"} >} Estimate)
OR
= Sum(Aggr(Sum({<Quote_number = {"= Max(Quote_number)"} >} Estimate), Description))
Thanks, as always, for your help,
Ben
