Currency issue using SQL query on map visualisation

1

I had a question where I needed to work out what the percentage of my 'grouped_by' summary values (Sum of price_paid) were of the total of a whole dataset.
I therefore converted my question to an SQL query to accomplish this. However the value I was summing needs to be a currency value (this is set up in the database 'Data Model'). This was fine in the original question but in SQL on the map visualisation, there are no options to prefix the values and if I use 'to_char' to add "£" as a prefix in the query, the whole column isn't recognised/available to select as the metric field.

Is there another way I should be approaching this?

SQL Query

SELECT "Locations"."country_code" AS "Locations__country_code", date_trunc('year', "public"."posts"."post_timestamp") AS "post_timestamp", to_char(sum("public"."posts"."price_paid")::float / '100', 'L999G999G999D99') AS "Sum of Price Paid", (sum("public"."posts"."price_paid") / (SELECT sum("public"."posts"."price_paid")::float FROM "public"."posts") * '100') AS "Ratio %"
FROM "public"."posts"
LEFT JOIN "public"."locations" "Locations" ON "public"."posts"."location_id" = "Locations"."id"
WHERE ("public"."posts"."post_timestamp" >= date_trunc('year', (now() + (INTERVAL '-1 year')))
   AND "public"."posts"."post_timestamp" < date_trunc('year', now()))
GROUP BY "Locations"."country_code", date_trunc('year', "public"."posts"."post_timestamp")
ORDER BY "Locations"."country_code" ASC, date_trunc('year', "public"."posts"."post_timestamp") ASC

Diagnostic info

{
  "browser-info": {
    "language": "en-GB",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.17+8-LTS",
    "java.vendor": "Azul Systems, Inc.",
    "java.vendor.url": "http://www.azul.com/",
    "java.version": "11.0.17",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.17+8-LTS",
    "os.name": "Linux",
    "os.version": "4.4.0-1104-aws",
    "user.language": "en",
    "user.timezone": "Etc/UTC"
  },
  "metabase-info": {
    "databases": [
      "postgres",
      "h2"
    ],
    "hosting-env": "heroku",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "13.9 (Ubuntu 13.9-1.pgdg20.04+1)"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.5.0"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2022-12-07",
      "tag": "v0.45.1",
      "branch": "release-x.45.x",
      "hash": "019d31c"
    },
    "settings": {
      "report-timezone": null
    }
  }
}
2

Hi @ed.s
You can change the formatting of the column in the SQL question.
Or you can convert the SQL question to a Model, where you can change formatting or mapping.
It's currently not possible to define currency based on another column:
https://github.com/metabase/metabase/issues/9879 - upvote by clicking :+1: on the first post

3

Hi @flamber
I believe this is what I did (Changing the formatting of the column the line - to_char(sum("public"."posts"."price_paid")::float / '100', 'L999G999G999D99') AS "Sum of Price Paid" ) but this removes it from the options. Are you saying that this should work?

4

@ed.s I'm not referring to casting, but to visualization formatting settings:
https://www.metabase.com/docs/latest/questions/sharing/visualizing-results#column-formatting-options

5

Hi @flamber

I understand now. Sorry.

FYI, when selecting various options on the column formatting area. The selection is ignored. But sometimes it seems, if you refresh the page, it has taken the selection into account.

6

@ed.s I cannot reproduce. Check your browser developer console for errors. Try without any browser extensions. Open an issue with a video showing the problem including all other info https://github.com/metabase/metabase/issues/new/choose.