Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
ID | CODE_REGION | DATE | CUSTOMER_ID | AMOUNT |
79 | C | 11/09/2017 | 676109 | 2341,07 |
7 | C | 14/09/2017 | 447280 | 2341,07 |
62 | C | 19/09/2017 | 643170 | 2341,07 |
78 | C | 19/09/2017 | 643170 | 2341,07 |
then
Sep 2017 | C | 78 | 19/09/2017 | 643170 | 2341,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.
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 2017 | A | 358992 | 27/08/2017 | 2087,76 |
B | 968747 | 23/08/2017 | 2417,52 | |
C | 643170 | 07/08/2017 | 2498,27 | |
sep 2017 | A | 593205 | 08/09/2017 | 2326,98 |
B | 529757 | 22/09/2017 | 2459,31 | |
C | 643170 | 19/09/2017 | 2341,07 | |
okt 2017 | A | 593205 | 09/10/2017 | 2395,13 |
B | 730574 | 07/10/2017 | 2119,61 | |
C | 676109 | 14/10/2017 | 2370,9 |
So are you looking to just show 1 row without any selection or is do you need this by Month and Region?
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.
Check the attached
Sunny,
Thanks
But in your solution I get 3 records for region C, September 2017, not one single one for region C, September 2017
In my original qvw the result is correct. My only question is, can it be done more efficient
R.W.
May be flag this in the script but then selections won't work.... is that something you can work with?
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.
May be swuehl can offer his expertise here
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 2017 | A | 593205 | 08/09/2017 | 2326,98 |
sep 2017 | B | 529757 | 22/09/2017 | 2459,31 |
sep 2017 | C | 643170 | 19/09/2017 | 2341,07 |
okt 2017 | A | 927102 | 02/10/2017 | 2395,13 |
okt 2017 | B | 730574 | 07/10/2017 | 2119,61 |
okt 2017 | C | 676109 | 14/10/2017 | 2370,9 |
aug 2017 | A | 358992 | 27/08/2017 | 2087,76 |
aug 2017 | B | 968747 | 23/08/2017 | 2417,52 |
aug 2017 | C | 643170 | 07/08/2017 | 2498,27 |
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.
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.