topic Re: Aggregation with FirstSortedValue in QlikView

Aggregation with FirstSortedValue

blpetosa — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aggregation with FirstSortedValue

sunny_talwar — Thu, 29 Mar 2018 15:47:23 GMT

May be this

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

Re: Aggregation with FirstSortedValue

blpetosa — Thu, 29 Mar 2018 17:03:27 GMT

Sunny,

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

Re: Aggregation with FirstSortedValue

blpetosa — Mon, 14 May 2018 20:20:57 GMT

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

sunny_talwar — Tue, 15 May 2018 01:45:02 GMT

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

Re: Aggregation with FirstSortedValue

blpetosa — Tue, 15 May 2018 10:58:02 GMT

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)

= Sum(Aggr(Sum({<Quote_number = {"= Max(Quote_number)"} >} Estimate), Description))

Thanks, as always, for your help,

Ben

Re: Aggregation with FirstSortedValue

sunny_talwar — Tue, 15 May 2018 11:12:10 GMT

Try this

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

Re: Aggregation with FirstSortedValue

blpetosa — Wed, 16 May 2018 20:04:10 GMT

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.