Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm using a pivot table and have a question about sorting. Here is a screenshot of the table, where I'm sorting by Agmnt ID, Obligation Date and Unit Location:
As you can see the date "6/30/2020" has two obligations, one which has been 'Fufilled' (by "Ashland"0 and one which has not (and therefore is null).
My question is, how can I get the records to sort so that "Ashland" is listed first, and the null field is listed second?
I thought it would be easy to just go into the sort panel for the "unit Location" field and sort alphabetically as shown below, but it does not affect the sort sequence:
Thanks for any help or ideas anyone can offer!
I think it is showing square brackets around the whole thing. Make sure to remove them..
What if you try like this
Dual([Unit Location], If(Len(Trim([Unit Location])) = 0, 0, 1))
and then sort numerically
HI Sunny,
Hope you are doing well and thanks for your reply.
Would I place that expression in my load script editor?
Thanks again!
You can do that or you can put this as a calculated dimension.
Sunny,
The Location field is actually 'Unit Location Dim', and when I use the following expression, it is not populating the field with the correct location information to match with the Agreement ID or fulfilled obligation:
In case it is helpful, here is the expression in my script editor that defines Unit Location:
Dual("Unit Location", AutoNumber("Unit Location"&"Address Line 1")) as "Unit Location Dim",
Oh so it is already a Dual field. Have you tried sorting this numerically?
Yes, I have tried that. As a matter of fact, I added in a numeric unique identifier (PS#), and it doesn't acknowledge the desirable sort even with that field in the table.
But, back to your question, I have tried sorting numerically on the 'Unit Location" field as follows, and do not get the desired result:
Is the dimension for this table coming from multiple tables? Would you be able to share your qvf to take a look at?
The dimension is from one other table, and I'm not sure how to share a qvf file...I'm apologize...