Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rudywelvaert
Creator
Creator

Pivot table with biggest amounts and the id of the biggest amount

Hi,

I have a table that contains an id (unique per record), a customer id, an amount, a data and a region code

I like to add a pivot table that shows me the largest amount per region and per month, but also the id, customer id and date of that largest amount.

The largest amount can occur more than once per month and region, in that case I select on the latest date, if the date occurs more than once then I want the largest id

So if the largest amount in September 2017 in region C is 2341.07 and these are the records I find:

IDCODE_REGIONDATECUSTOMER_IDAMOUNT
79C11/09/20176761092341,07
7C14/09/20174472802341,07
62C19/09/20176431702341,07
78C19/09/20176431702341,07

then

Sep 2017C7819/09/20176431702341,07


must be shown as the line for September 2017 for region C

Attached is an example qvw with the data loaded

Is there a better way to do this?

Thanks

R.W.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Ok, that has clarified the version of your application.

Still, I think Sunny has posted a solution to your request, it seems that you missed to open his version, instead looking at the same wrong qvw you've posted at the beginning.

Well, it doesn't really matter, but maybe take a few seconds to double check your findings before posting a reply to my post...

I've already posted a somewhat shorter version of a script based solution in my previous reply using FirstSortedValue() aggregation function to identify the record with the max amount and using the sort priority.

FirstsortedValue(DATE & '-'&ID, -(AMOUNT*1E9+DATE*1E3+ID) ) as MAX_KEY

(AMOUNT*1E9+DATE*1E3+ID) is defining the sort here and I've used a weighted combination of AMOUNT, DATE and ID to achieve your requirements. I assumed that ID is smaller some 1000, so I've multiplied DATE with 1000 to get DATE a higher priority than ID.

DATE is in our days in the order of 40000.

I've multiplied AMOUNT by 1E9 to get AMOUNT a higher priority than DATE (assuming the max AMOUNT is larger than 1).


So your fact table is sorted by AMOUNT, DATE, ID (descending) then the first entry will be used to return a combined key of DATE and ID, which is used for the JOIN of your fact details.


You can use the same FirstSortedValue() approach in a chart, without using an aggregated table in the script. This approach will be sensitive to user selections.


=Monthname(DATE) CODE_REGION = FirstsortedValue(CUSTOMER_ID, -(AMOUNT*1E9+DATE*1E3+ID) ) = FirstsortedValue(DATE, -(AMOUNT*1E9+DATE*1E3+ID) ) Max(AMOUNT)
aug 2017A35899227/08/20172087,76
B96874723/08/20172417,52
C64317007/08/20172498,27
sep 2017A59320508/09/20172326,98
B52975722/09/20172459,31
C64317019/09/20172341,07
okt 2017A59320509/10/20172395,13
B73057407/10/20172119,61
C67610914/10/20172370,9



View solution in original post

10 Replies
sunny_talwar

So are you looking to just show 1 row without any selection or is do you need this by Month and Region?

rudywelvaert
Creator
Creator
Author

Sunny,

I need for every month and every region  ALL the fields: ID, CODE_ REGION, DATE, CUSTOMER_ID and AMOUNT of the record with the largest amount per month and per region.

Thanks

R.W.

sunny_talwar

Check the attached

Capture.PNG

rudywelvaert
Creator
Creator
Author

Sunny,

Thanks

But in your solution I get 3 records for region C, September 2017, not one single one for region C, September 2017

C_Sep_2017.png

In my original qvw the result is correct. My only question is, can it be done more efficient

R.W.

sunny_talwar

May be flag this in the script but then selections won't work.... is that something you can work with?

rudywelvaert
Creator
Creator
Author

Sunny,

Thanks

But I can't work with that.
I'm looking for the exact same result as in my original qvw, but in a more efficient/shorter way.

R.W.

sunny_talwar

May be swuehl‌ can offer his expertise here

swuehl
MVP
MVP

I must admit I am confused...

Rudy, your original file seems to show three lines for Sep 2017, Region C, so it seems to not fulfill your requirement.

While Sunny's sample seems to show the correct chart (you need to look at the bottom right chart).

Note that your pasted table seems to refer to the original table (note the different column headings).

There might be different approaches to do what Sunny did, but since I am most confused, I think you need to clarify on your current application and requirement.

Besides this, do you want to prepare a table in the script like you did in your original file, or do you want a completely chart based solution?

I am attaching a version for the first (script based, but this can be easily adapted to a chart only solution).

Month Region Customer Date Amount
sep 2017A59320508/09/20172326,98
sep 2017B52975722/09/20172459,31
sep 2017C64317019/09/20172341,07
okt 2017A92710202/10/20172395,13
okt 2017B73057407/10/20172119,61
okt 2017C67610914/10/20172370,9
aug 2017A35899227/08/20172087,76
aug 2017B96874723/08/20172417,52
aug 2017C64317007/08/20172498,27
rudywelvaert
Creator
Creator
Author

Stefan,

Thanks.

Sorry for the confusion.

I made the mistake to attach the wrong qvw.

Here I attached the qvw who does show only one line per region and month.

chart.png

script.png

I found this myself quite elaborated and I wondered if there was a way to make it more compact and efficient.

I'm looking for a solution where I want to prepare a table in the script, but if it's more efficient to do it with expressions in the chart that's great too.

Thanks

R.W.