Pivot Table Issues - Needs Work
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
Comments
-
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.
1 -
@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.If this a suggestion above helps the customer resolve the issue on their end, please provide the outcome of this case for addressing any future issues that may arise.
0 -
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."
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.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive