Here is an example of my data set (it is much larger):
estimate_number est_rev_nbr description amount
307 0 Hotel 500
307 1 Hotel 1000
307 2 Hotel 1500
307 0 Flight 200
307 1 Flight 300
307 2 Flight 400
307 3 Flight 500
The max est_rev_nbr is different depending on the description. I am looking to get Hotel($1500) and Flight($500), and there are many more components not shown here. I would also like this in a Pivot Table visualization as a measure, so I was using "sum" to just give me the value. I was thinking something like:
I know it has been some time, and not sure if you get a notification with this reply, but figure it is worth a shot. Your formula above works great for the main use case, which is a KPI queried by an internal job code. It doesn't seem to work in a table visualization though, and there is no query (shows all of the internal jobs in list form). I have tried a bunch of other things, and can't get it to work. Is there a different formula that would work for a table?
P.S. For the data above, there is a job_code (say 9000), that would be at the beginning of each row.
Thanks in advance, and I will try the community if I don't hear back from you.
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):
There are 3 different jobs here. My desired output is (not in this format):
1000 Hotel: $1500
2000 Food: $1100
3000 Flight: $900
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,
Sunny, thank you! This is exactly what I needed. I really appreciate your help with this. I was in all day meetings yesterday, and was dying to try it out. I went to my first Qlik conference this year, and plan on going from here on out. I would like to thank you in person some time, if you attend these.