Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I'm hoping there is an easy way to implement sorting on this pivot table. Basically, I'd like each expense type column to be sorted at the users will. So using Lodging as an example I'd like the user to click something and the data would be sorted by descending transaction amount for the Lodging field. Any help is appreciated. I've seen some rather complicated entries on this topic on this board, and since I'm a novice was hoping there is an easier way?? Thanks!
Can you check if attached is what you want
Script:
Table:
CrossTable(Type, Data, 2)
LOAD AcctNum,
Name,
[Car Rental],
Lodging,
Airline
FROM
[PivotExample.xlsx]
(ooxml, embedded labels, table is Sheet1);
Sort:
LOAD * Inline [
Sort
Car Rental
Lodging
Airline
];
Here Sort field can be used to decide which field you want to sort by.
By Lodging
By Airline
By Car Rental
Used a sort expression
Sum({<Type =p(Sort)>} Data)
Is it possible to share a sample with what the expectation is?
Here is a simple snapshot that I've done in Excel. Notice that the Lodging column is sorted in descending order. I'd like to have the user be able to select which column they would like sorted. Hope this helped. I cannot share the live data.
Can you share the above data in the Excel format atleast
Sure - here you go.........
Can you check if attached is what you want
Script:
Table:
CrossTable(Type, Data, 2)
LOAD AcctNum,
Name,
[Car Rental],
Lodging,
Airline
FROM
[PivotExample.xlsx]
(ooxml, embedded labels, table is Sheet1);
Sort:
LOAD * Inline [
Sort
Car Rental
Lodging
Airline
];
Here Sort field can be used to decide which field you want to sort by.
By Lodging
By Airline
By Car Rental
Used a sort expression
Sum({<Type =p(Sort)>} Data)
Perfect...........I feel like I'll never learn the proper syntax for these expressions. Thanks for all your help!!!!
No problem at all, I am glad to help
Andrea - wow - that will simplify things!! Thank you!!!