My columns also have text and numbers. I want to sum the numbers, ignore the "N/As"
I would suggest creating a Magic ETL and clean your data so that you have a numeric column and then you can use the built in chart properties to total different columns. You could also create a beast mode field to do the same thing. Replace your N/As with NULL should allow for proper counting.
Thanks Mark! We do want to show the N/As; is there a way to only calculate on the rows that have a number?
Unfortunately, not that I have seen. Would be a nice enhancement. I have seen workaround for this where you create a dashboard page and create some single value cards to show the counts you want and put them around your main card. The interactive filters will allow you to keep all cards in sync if someone filters on something. Not sure if building a dashboard page will work for your use case, but that is one idea for you.
Thanks again Mark!
So technically you could hack it a little bit to sum all of your values together while ignoring but it gets a bit wonky as the Total row is manually calculated and would need to be a separate row in your dataset. If the user does any sorting the Total row wouldn't be at the bottom anymore. You'd essentially conditionally display the total across the entire dataset using a window function if your key field = 'Total'.
SUM will only add numerical values so it will automatically ignore your N/A values.
CASE WHEN `Name` = 'Total' THEN SUM(SUM(`NA Numbers`)) OVER () ELSE `NA Numbers` END
Also, by utilizing a window function in a beast mode it'll properly handle any filtering done to the cards instead of doing it within an ETL.
Cheers. My table card is a drilldown, so I think I have to do it in Beast mode.
It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!