Workaround to Pivot tables pushing non-valued categories to the bottom.

Samuel. · February 2023

Hi,

I'm currently trying to build out a pivot table with custom sorts on both the row and column values. As has been mentioned before on the dojo; pivot tables will always override the sort so that non-values will display last even with a custom sort including the column/row headers.

Has anyone found a workaround to this interaction without the solution simply being... "JuSt POPuLAte tHE fIELd". 0 values are just as important as non-0 values.

Best,

Stuck

MichelleH · February 2023

@Stuck To clarify, by "non-valued" do you mean the the value is zero, null, or an empty string? Regardless, you can get around this by hard-coding a value using a CASE or IFNULL function.

That's probably not the answer you're looking for, but this is by far the simplest way to accomplish what you need. You can still distinguish which values you're overriding based on the value you hard-code in while preserving functionality.

Samuel. · February 2023

Thanks for your responce @MichelleH

When I say "non-valued" I mean anything that a non-zero/non-null input

Even when hard coding the value as a 0, it seems that the pivot tables ignore these values in the sort.

Something as simple as

CASE
WHEN sum(`value measure`) IS NULL THEN 0
ELSE sum(`value measure`) 
END

Also tested replacing the IS NULL with = 0 and testing SUM(IFNULL(`value measure`,0))

All methods have validated as acceptable outputs but in each instance I can only get 1 of my 2 descriptors to sort correctly. The second sort always sorts the column/row that have non-zero/null values and always forces the 0 values to the bottom.

Unless I'm missing a simple trick?

MichelleH · February 2023

@Stuck Of the options you're testing, SUM(IFNULL(`value measure`,0)) should give you the best result. The reason your case statement didn't work is because you had aggregate functions inside the case statement, which can cause sorting issues. Whenever you need to use an aggregate and a case in the same function, it's best to put the case inside the aggregate like this:

sum(case 
when `value measure` is null then 0 
else `value measure` 
end)

Samuel. · February 2023

@MichelleH Thanks for your response - I tested that above and it's still not producing the desired results. My current solution is to force sort the column in table as opposed to using a sorting function as one of my two fields is alphabetical. A in sorting "custom sort" feature being added would be so powerful honestly.

MichelleH · February 2023

@Stuck Can you elaborate on how you "force sorted"? You can create a beast mode to create a custom sort order like below:

case 
when X then 1
when Y then 2
when Z then 3
else 4 
end

Also, can you check that the aggregation used in your sort fields aligns with the aggregation of the same fields in the pivot table? If any of your sort values are set to "No aggregation", then that could be causing issues as well.

Samuel. · February 2023

Exactly that form of case statement for a forced sort - i've tried both a numeric and alphabetical approach to force sort, replacing the 1 with an A, 2 with B etc. with the same results. No aggregation is present - as I said prior I can swap both my column and row sort around and the first sort will work as intended, it's only the second forced sort that doesn't work as intended.

RE aggregation of the sort field - our field value has no aggregation on the object format as any aggregation happens in our beast mode and the values reconcile back to what we expect. We aren't trying to sort based on the field values aggregation - simply on a specified order as described above, one is the row is alphabetical and the column is custom based on requirements. Hence why I've used in card sorting to fix the current visual.

MichelleH · February 2023

@Stuck A couple other things to consider:

Does your custom sorting beast mode take into account the null handling used in your value field?
If you have multiple sort fields in your card, are you able to achieve the desired behavior by rearranging the order that the fields are listed?

If neither of these get you what you need, then I'd suggest posting a screenshot of your analyzer settings so we can dig into it further.

Samuel. · February 2023

@MichelleH

I think point 1 is exactly the issue - even with my non-null aggregate value function its not behaving.

DOMO still isn't recognising the 0/null values when applying the sort. The order of operation for the sort that seems to be taking place is...

It takes the first column of the first sort and then applies the second sort to those rows that have non-0 values. It then moves onto the second column of the first sort and then applies the second sort to the remaining unsorted rows that are non-0. Then onto the third column and so forth until either all columns are sorted or until all cell values are acounted for.

How would you adjust a custom sort beast mode to include the null handling when the non-null aggregate value function is not being considered by DOMO?

As for point 2, if I swap the sorts, the same issue happens just the other way around...

It takes the first row of the first sort and then applies the second sort to those columns that have non-0 values. It then moves onto the second row of the first sort and then applies the second sort to the remaining unsorted columns that are non-0. Then onto the third row and so forth until either all rows are sorted or until all cell values are acounted for.

The solution I have currently that is 'Good Enough' is to use my original sort process with the column values and in this case, because the row values want to be sorted alphabetically, to check "Enable Sorting" in General Chart Properties and force the table to sort alphabetically that way - the only issue with this is that the blank row value populates the top and if I create a custom sort to force blank to the bottom the alphabetical custom sort overrides this regardless.

Currently anything that is blank is now categorised under a 'zOther' to force it to the bottom. Just not the tidiest.

I'd love to screenshot and give visual aid to this process but unfortuntely my organisation doesn't allow any images to be uploaded to the web for security.

To replicate my issue just create a 3 x 3 pivot with the form:

and try to get it to sort to

The outcome I'd expect DOMO to produce though with the numbers (columns) sorted first would be

The outcome I'd expect DOMO to produce though with the letters (rows) sorted first would be

Thats why I'm currently sorting the columns and then using the in-table sort for an alphabetical sort.

Rob_E · June 18

Maybe, I'm mis-reading the post, but I see the sorting issue when there are missing attributes in the source file: For example, there is no record with "a" and "1" attribute values.

So, happy path is that the source file has a record for all attribute combinations, like this:

With this file, I have no problem sorting ascending or descending and keeping the serial order of the two attributes:

Unhappy path is when the source file is missing attribute combinations when there is no count:

With this file, I am not able to guarantee the serial order of the two attributes

The work around I use is to generate 0-count records for all combinations in a dataflow, but that feels like make-work. Surely there must be a way to force the sort order when the source file is missing records with attribute values.

Workaround to Pivot tables pushing non-valued categories to the bottom.

Comments

Categories