Hi Hive Mind,
Please help!
I have a data set for the amount of people per group. Each group is made from a matrix of their insurance benefit, gender, race, and age group. I've been asked to provide a line graph of membership over time and to include a look-back of the counts for the previous year in the tooltip ("this time last year"). I succeeded in doing so by using table calcs (including a date filter from a table calc). However as you know, table calc filters can only apply to one sheet at a time. I worked around this limitation in the dashboard design, but the requestor would prefer other designs that go against the table calc limitations.
That brought me to a custom SQL query. I'm joining the table to itself. I have year joining against last year. The query works perfectly in SAS using an Oracle pass-through statement, but I'm getting weird numbers in Tableau through my custom SQL query that are off by 5-10 in each case. For the life of me I cannot figure out why this join is making up numbers that don't exist in the data. Does anyone have any insight on this?
I've provided my code below (with table name omitted) and a few screenshots to see the weird error. Thank you!
SELECT T1.*
,ADD_MONTHS(T1.MID_MONTH,-12) as PY
,T2.MEMBERS as PY_MEMBERS
FROM TABLE T1
INNER JOIN TABLE T2
ON T1.MID_MONTH = ADD_MONTHS(T2.MID_MONTH,-12)
AND T1.AGE_GROUP = T2.AGE_GROUP
AND T1.GRPNUM = T2.GRPNUM
AND T1.RACECD = T2.RACECD
AND T1.SEXCOD = T2.SEXCOD
Hey Ken,
Unfortunately I can't provide more details than those previously offered. I'll have to stick with the table calcs on this one since I simply cannot understand why the code will run in other SQL-based programs but not in the Tableau SQL query. Thank you for your time on this! At the very least, I know I'm not losing it!
