Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

blpetosa
New Contributor III

Aggregation with FirstSortedValue

Hello Everyone,

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:


sum(FirstSortedValue(amount, -est_rev_nbr)


I appreciate any help with this.


Thanks,


Ben

1 Solution

Accepted Solutions

Re: Aggregation with FirstSortedValue

May be this

Sum(Aggr(FirstSortedValue(amount, -est_rev_nbr), description))

7 Replies

Re: Aggregation with FirstSortedValue

May be this

Sum(Aggr(FirstSortedValue(amount, -est_rev_nbr), description))

blpetosa
New Contributor III

Re: Aggregation with FirstSortedValue

Sunny,

You are always such a great help! Thanks for the swift, and correct response!

blpetosa
New Contributor III

Re: Aggregation with FirstSortedValue

Sunny,

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.

Ben

Re: Aggregation with FirstSortedValue

Would you be able to share more information? like what is your dimensions? May be share a sample if possible

blpetosa
New Contributor III

Re: Aggregation with FirstSortedValue

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

Re: Aggregation with FirstSortedValue

Try this

Sum(Aggr(If(est_rev_nbr = Max(TOTAL <job_number> est_rev_nbr), Sum(amount)), job_number, description, est_rev_nbr))

Capture.PNG

blpetosa
New Contributor III

Re: Aggregation with FirstSortedValue

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.

Community Browser