Pivot Table Issues - Needs Work

Mickey

Hi there,

I've been using pivot tables for a while, and I just noticed a weird way in which Pivot Table cards specifically sort their data. I have the retail sales of a bunch of different brands, and then I sort them by month using a pivot table. When I go to sort them or filter by retail sales, it sorts or filters based on the MONTH that those retail sales happened.

Example: I have two brands that have sales for January through April.

Brand 1: $125 - Jan, $80 - Feb, $215 - Mar, $15 - Apr || Total - $435

Brand 2: $250 - Jan, $40 - Feb, $100 - Mar, $30 - Apr || Total - $420

SORTING ISSUE: If I sort by total retail descending, in the pivot table, Brand 2 is now above Brand 1 because it made $250 in January (more than Brand 1 in the same month). From what I can find, there is no way to sort by the true total unless you click the sorting arrows within the total column that the card can generate. If I have another card that produces the This Year vs Last Year retail sales as a percentage, where there is no Retail total column to sort by, this means I will never be able to sort by the true Retail total, only whichever brand made the most in ONE of the months given.

FILTER ISSUE: If I apply a filter to the card, Retail > $100, the Pivot table marks any month with less than $100 as $0, but keeps any months that made over that. So Brand 1 goes from $435 to $340 because it made over $100 in Jan & Mar, but not Feb & Apr (it puts $0 for those months). For Brand 2 the total would be $350 for the same reasons as Brand 1, and now the total is saying it made MORE than Brand 1 because of the filter. I don't want the filter to apply to months, just the total amount.

I understand this is a long explanation for a very specific fix, but it is an issue that will be encountered a lot in the future for us and there seems to be no way around this problem. It's an issue within the card itself that we cannot fix

marcel_luthi

Hi, I haven't tried yet, but this seems to be something you could workaround by creating a beast mode using the fixed function, that will return you the true total by brand, something like:

SUM(SUM(`Retail`) FIXED (BY `Brand`))

And then using this beast mode for both the sorting and filter criteria. Might be worth giving it a shot.

AKnowles

@Mickey If you look at the Knowledge article they have on Pivot Tables, this is a known issue I believe:

https://domo-support.domo.com/s/article/360043429473?language=en_US

You are supposed to use the sorting arrows when you are using a pivot table.

Mickey

UPDATE: I was using the below formula as a sort and filter, which was working for a while. However, I'm now encountering a strange issue that will not let me save the card if I have this formula applied as a filter (it was working before). It works as a sort, it works if I put the beast mode into the pivot table as a value, but once it is in the filter the card will not save. To clarify, the card itself accepts the filter and works within the card editor. I can see the card with the filter applied and all values look good. It is only when I try to save the card that I get the message: "There was a problem saving your card. If the problem persists, contact the Domo support team." 

1. SUM(SUM(`Retail`) FIXED (BY `Brand`))

I think there was an update to Domo which does not allow the FIXED function to be in filters. Why is that? I can see it work perfectly fine within the card editor. It's as if Domo is just being petty with this one issue. This was the only way to filter how I needed to filter, and now it does not work.