Tableau中RAWSQL_REAL无法传入表计算的IRR计算求助

• 现有两个通过Tableau表计算生成的字段：
  • Cashflow STR ARRAY：

    PREVIOUS_VALUE("") + "," + STR(Sum([Cashflow]))
    

  • Cashflow Date Array：

    PREVIOUS_VALUE("") + "," + STR(min([Cashflow Date]))
    

• 尝试将上述字段传入自定义Python UDF计算IRR，调用语句：

  RAWSQL_REAL(
      "SELECT calculate_irr(%1, %2)",[Cashflow STR ARRAY],[Cashflow Date STR ARRAY])
  

• 核心问题：Tableau不允许将表计算作为参数传入RAWSQL_REAL函数，且Tableau Prep、自定义SQL方案均未生效。
• 自定义Python UDF代码（数据库端）：

  CREATE OR REPLACE FUNCTION calculate_irr(cashflow_string STRING, date_string STRING)
  RETURNS FLOAT
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.8'
  PACKAGES = ('numpy', 'scipy')
  HANDLER = 'xirr'
  AS
  $$import numpy as np
  import scipy.optimize
  from datetime import datetime
  
  def xnpv(rate, values, dates, daycount=365):
      # Calculate the Net Present Value for a given rate
      d0 = dates[0]
      return sum(vi / (1.0 + rate) ** ((di - d0).days / daycount) for vi, di in zip(values, dates))
  
  def xirr(cashflow_string, date_string):
      # Split strings into lists
      cashflow_values = list(map(float, cashflow_string.split(',')))
      cashflow_dates = [datetime.strptime(date.strip(), '%m/%d/%Y') for date in date_string.split(',')]
  
      # Default parameters
      daycount = 365
      guess = 0.1
      maxiters = 10000
      a = -0.99
      b = 1.0
  
      # Check if all cash flows are positive or negative
      cf = np.array(cashflow_values)
      if np.all(cf >= 0) or np.all(cf <= 0):
          return float('nan')
  
      def safe_xnpv(rate):
          # Ensure rate is within a valid range
          if rate <= -1.0:
              return float('inf')
          return xnpv(rate, cashflow_values, cashflow_dates, daycount)
  
      try:
          # Try using the Newton-Raphson method
          output = scipy.optimize.newton(lambda r: safe_xnpv(r),
                                       x0=guess, maxiter=maxiters, full_output=True, disp=True)[0]
      except RuntimeError:
          try:
              # Try using the Brent's method
              output = scipy.optimize.brentq(lambda r: safe_xnpv(r),
                                           a=a, b=b, maxiter=maxiters, full_output=True, disp=True)[0]
          except:
              # Fallback to fsolve if other methods fail
              result = scipy.optimize.fsolve(lambda r: safe_xnpv(r),
                                           x0=guess, maxfev=maxiters, full_output=True)
              if result[2] == 1:  # Check if the solution converged
                  output = result[0][0]
              else:
                  output = np.nan
  
      return output$$;
  

方法1：用LOD表达式替代表计算，生成可传入RAWSQL的字段 #

Tableau的RAWSQL系列函数支持使用LOD表达式的结果作为参数，因此可以将原表计算逻辑替换为LOD+数据库字符串聚合函数：

步骤： #

1. 替换表计算为LOD聚合：
   根据动态筛选维度（如行业、公司ID等），用INCLUDE或EXCLUDE LOD生成单条记录的现金流和日期字符串：

   • 单条现金流字符串：

     {INCLUDE [行业], [公司ID]: STR(SUM([Cashflow]))}
     

   • 单条日期字符串：

     {INCLUDE [行业], [公司ID]: STR(MIN([Cashflow Date]))}
     

   注：INCLUDE后的维度需包含所有需要动态筛选的字段。

2. 用数据库聚合函数拼接成数组字符串：
   利用数据库支持的字符串聚合函数（如Snowflake/PostgreSQL/SQL Server的STRING_AGG），按分组拼接所有记录：

   • 现金流数组字符串：

     RAWSQL_STR("STRING_AGG(%1, ',') OVER (PARTITION BY [行业], [公司ID] ORDER BY [Cashflow Date])", [单条现金流字符串])
     

   • 日期数组字符串：

     RAWSQL_STR("STRING_AGG(%1, ',') OVER (PARTITION BY [行业], [公司ID] ORDER BY [Cashflow Date])", [单条日期字符串])
     

3. 传入RAWSQL_REAL调用UDF：
   此时生成的数组字符串是数据库层面的计算结果，而非Tableau表计算，可直接传入RAWSQL_REAL：

   RAWSQL_REAL(
       "SELECT calculate_irr(%1, %2)",[现金流数组字符串],[日期数组字符串])
   

方法2：使用TabPy直接在Tableau中运行IRR计算 #

TabPy是Tableau的Python扩展，支持将Tableau的计算字段（包括表计算）作为输入，直接调用Python代码计算结果，无需依赖数据库UDF：

步骤： #

1. 启动并连接TabPy：

   • 安装TabPy：pip install tabpy
   • 启动服务：tabpy
   • 在Tableau中连接TabPy：点击「帮助」→「设置和性能」→「管理外部服务连接」，添加TabPy服务地址（默认http://localhost:9004）。

2. 部署IRR计算函数到TabPy：
   在Python终端中执行以下代码，将xirr函数部署到TabPy：

   import tabpy_client
   import numpy as np
   import scipy.optimize
   from datetime import datetime
   
   def xnpv(rate, values, dates, daycount=365):
       d0 = dates[0]
       return sum(vi / (1.0 + rate) ** ((di - d0).days / daycount) for vi, di in zip(values, dates))
   
   def xirr(cashflow_string, date_string):
       cashflow_values = list(map(float, cashflow_string.split(',')))
       cashflow_dates = [datetime.strptime(date.strip(), '%m/%d/%Y') for date in date_string.split(',')]
   
       daycount = 365
       guess = 0.1
       maxiters = 10000
       a = -0.99
       b = 1.0
   
       cf = np.array(cashflow_values)
       if np.all(cf >= 0) or np.all(cf <= 0):
           return float('nan')
   
       def safe_xnpv(rate):
           if rate <= -1.0:
               return float('inf')
           return xnpv(rate, cashflow_values, cashflow_dates, daycount)
   
       try:
           output = scipy.optimize.newton(lambda r: safe_xnpv(r),
                                        x0=guess, maxiter=maxiters, full_output=True, disp=True)[0]
       except RuntimeError:
           try:
               output = scipy.optimize.brentq(lambda r: safe_xnpv(r),
                                            a=a, b=b, maxiter=maxiters, full_output=True, disp=True)[0]
           except:
               result = scipy.optimize.fsolve(lambda r: safe_xnpv(r),
                                            x0=guess, maxfev=maxiters, full_output=True)
               if result[2] == 1:
                   output = result[0][0]
               else:
                   output = np.nan
   
       return output
   
   # 连接TabPy并部署函数
   client = tabpy_client.Client('http://localhost:9004')
   client.deploy('calculate_xirr', xirr, 'Calculates XIRR from cashflow string and date string', override=True)
   

3. 在Tableau中创建计算字段调用函数：
   创建新的计算字段，直接传入原表计算生成的Cashflow STR ARRAY和Cashflow Date Array：

   SCRIPT_REAL(
       "return tabpy.query('calculate_xirr', _arg1, _arg2)['response']",
       [Cashflow STR ARRAY],
       [Cashflow Date Array]
   )
   

方法3：数据源层预处理数组字符串 #

如果数据源支持窗口函数，可直接在数据源层面（如自定义SQL、视图）生成每个分组的现金流和日期数组字符串，Tableau直接使用这些预计算字段：

示例（Snowflake自定义SQL）：

WITH grouped_cashflows AS (
    SELECT
        [行业],
        [公司ID],
        STR(SUM([Cashflow])) AS cashflow_str,
        STR(MIN([Cashflow Date])) AS date_str,
        [Cashflow Date]
    FROM your_table
    GROUP BY [行业], [公司ID], [Cashflow Date]
)
SELECT
    [行业],
    [公司ID],
    STRING_AGG(cashflow_str, ',') OVER (PARTITION BY [行业], [公司ID] ORDER BY [Cashflow Date]) AS cashflow_array_str,
    STRING_AGG(date_str, ',') OVER (PARTITION BY [行业], [公司ID] ORDER BY [Cashflow Date]) AS date_array_str
FROM grouped_cashflows

将此自定义SQL作为Tableau数据源，直接用cashflow_array_str和date_array_str字段调用RAWSQL_REAL即可。

内容的提问来源于stack exchange，提问作者Alexander Kiknadze