Hello!
I have found something really strange.
I have a datasource that originally is a Postgres View where I have aggregated other sources into it. When aggregating I found one value to be quite wrong what I expected it to be. I took this value out with a simple SQL:
SELECT vw_ebr.market_id,
vw_ebr."month",
vw_ebr.revenue,
vw_ebr.click_type
FROM vw_ebr where vw_ebr.click_type like '%banner%' and "month" ='2015-05'
order by month
When running that directly in Postgres I get the correct result:
market_idmonthrevenueclick_type22015-056680.4461573451298312100banner12015-05646238.8898733049259605100banner
I export that to a csv file and import it as a data source in Tableau.. and i get:
Which is also correct.. great.
But when when running the exact same in Tableau using Custom SQL Query from Postgres i get:
Interesting huh? I have no idea what to do right now. Anyone?
Best regards
Ola
So I extracted the SQL for a part of the View n postgres and try to run it as a Custom SQL to see what it gives. I test it first in SQL terminal to see that it produces results.. and it does. I test it in Tableau.. not a single row. When i remove the date condition I get results, but wrong revenue.
This one sums up all the sales of type banner between a period and sets which month it is.
SELECT f.market_id,
p.date_from AS month,
'100banner'::text AS click_type,
((ceil(((100)::numeric * sum(((func_currency_convert_fx((f.currency_code)::bpchar, 'SEK'::bpchar, f.amount) / (((f.date_to - f.date_from) + 1))::numeric) * (((
CASE
WHEN (f.date_to < p.date_to) THEN f.date_to
ELSE p.date_to
END -
CASE
WHEN (f.date_from > p.date_from) THEN f.date_from
ELSE p.date_from
END) + 1))::numeric)))) / (100)::numeric))::numeric(16,2) AS revenue
FROM (( SELECT (i.date_from)::date AS date_from,
LEAST((now())::date, (((i.date_from + '1 mon'::interval))::date - 1)) AS date_to
FROM ( SELECT generate_series('2010-01-01 00:00:00+01'::timestamp with time zone, now(), '1 mon'::interval) AS date_from) i) p
JOIN accounting_banner_fee f ON (((f.date_from <= p.date_to) AND (f.date_to >= p.date_from))))
where market_id='1' and p.date_from ='2015-05-01'
GROUP BY f.market_id, p.date_from
order by month
- I have tried setting row 12 to ::text. Did not work
END) + 1))::numeric)))) / (100)::numeric))::text AS revenue
- I have tried to export the entire view into csv and importing it as a datascource. Works! (probably since it's just text and no calclulations.
Looking at the big picture, this is the entire view. Note: All except 100banners and 100 periodic works as expected.
SELECT e.market_id,
e.company_id,
e.month,
e.click_type,
sum(e.num_clicks) AS num_clicks,
sum(e.num_orders) AS num_orders,
((ceil(((100)::numeric * sum(func_currency_convert_fx(e.currency_code, 'SEK'::bpchar, e.revenue)))) / (100)::numeric))::text AS revenue
FROM ebr_cpo e
GROUP BY e.market_id, e.company_id, e.month, e.click_type
UNION
SELECT e.market_id,
e.company_id,
e.month,
e.click_type,
sum(e.num_clicks) AS num_clicks,
NULL::bigint AS num_orders,
((ceil(((100)::numeric * sum(func_currency_convert_fx((e.currency_code)::bpchar, 'SEK'::bpchar, e.revenue)))) / (100)::numeric))::text AS revenue
FROM ebr_cpc e
GROUP BY e.market_id, e.company_id, e.month, e.click_type
UNION
SELECT f.market_id,
NULL::integer AS company_id,
p.date_from AS month,
'100banner'::text AS click_type,
NULL::bigint AS num_clicks,
NULL::bigint AS num_orders,
((ceil(((100)::numeric * sum(((func_currency_convert_fx((f.currency_code)::bpchar, 'SEK'::bpchar, f.amount) / (((f.date_to - f.date_from) + 1))::numeric) * (((
CASE
WHEN (f.date_to < p.date_to) THEN f.date_to
ELSE p.date_to
END -
CASE
WHEN (f.date_from > p.date_from) THEN f.date_from
ELSE p.date_from
END) + 1))::numeric)))) / (100)::numeric))::text AS revenue
FROM (( SELECT (i.date_from)::date AS date_from,
LEAST((now())::date, (((i.date_from + '1 mon'::interval))::date - 1)) AS date_to
FROM ( SELECT generate_series('2010-01-01 00:00:00+01'::timestamp with time zone, now(), '1 mon'::interval) AS date_from) i) p
JOIN accounting_banner_fee f ON (((f.date_from <= p.date_to) AND (f.date_to >= p.date_from))))
GROUP BY f.market_id, p.date_from
UNION
SELECT pr.market_id,
NULL::integer AS company_id,
p.date_from AS month,
'100standalone'::text AS click_type,
NULL::bigint AS num_clicks,
NULL::bigint AS num_orders,
((ceil(((100)::numeric * sum(func_currency_convert_fx((f.currency_code)::bpchar, 'SEK'::bpchar, f.amount)))) / (100)::numeric))::text AS revenue
FROM ((( SELECT (i.date_from)::date AS date_from,
LEAST((now())::date, (((i.date_from + '1 mon'::interval))::date - 1)) AS date_to
FROM ( SELECT generate_series('2010-01-01 00:00:00+01'::timestamp with time zone, now(), '1 mon'::interval) AS date_from) i) p
JOIN promotion pr ON (((pr.start_date >= p.date_from) AND (pr.start_date <= p.date_to))))
JOIN accounting_promotion_fee f USING (market_id, promotion_id))
WHERE (NOT f.cancelled)
GROUP BY pr.market_id, p.date_from
UNION
SELECT f.market_id,
NULL::integer AS company_id,
p.date_from AS month,
'100periodic'::text AS click_type,
NULL::bigint AS num_clicks,
NULL::bigint AS num_orders,
((ceil(((100)::numeric * sum((((COALESCE(f.monthly, (0)::numeric) + COALESCE(f.quarterly, (0)::numeric)) + COALESCE(f.semiannual, (0)::numeric)) + ((COALESCE(f.annual, (0)::numeric) * (((p.date_to - p.date_from) + 1))::numeric) / ((((p.date_from + '1 mon'::interval))::date - p.date_from))::numeric))))) / (100)::numeric))::text AS revenue
FROM (( SELECT (i.date_from)::date AS date_from,
LEAST((now())::date, (((i.date_from + '1 mon'::interval))::date - 1)) AS date_to
FROM ( SELECT generate_series('2010-01-01 00:00:00+01'::timestamp with time zone, now(), '1 mon'::interval) AS date_from) i) p
JOIN accounting_periodic_fee f ON ((p.date_from = f.month)))
GROUP BY f.market_id, p.date_from
So, long post.. frustrating and frustrated
