Qlik Sense预算模型月度转YTD后GP计算错误技术求助

问题背景 #

现有预算P&L模型原本仅支持单月选择（基于Max(MONTH_NUM)），月度变量运行正常，但切换为年初至今（YTD）后，预算毛利（GP）计算完全错误。尝试过清除MONTH选择、调整日期范围、修改AGGR等操作，多选月份时GP值始终不正确或虚高。

原有月度变量（非YTD） #

预算预测（基础变量）vBudget_Forecast #

SUM(
  {
    <
      trx_type={"sales_forecast"},
      MONTH_NUM={$(=Max(MONTH_NUM))}
    >
  }
  quantity_original_uom * component_ytd_unit_price
)

预算预测-冷藏（Reefer）vBudget_Forecast_Reefer #

sum(
  {<account_pl_category_description={"Net Sales (Reefer)","Net Sales (Arla)"}>}
  aggr(
    $(vBudget_Forecast),
    account_company_key,
    account_pl_category_description
  )
)

预算预测-干货（Dry）vBudget_Forecast_Dry #

sum(
  {<account_pl_category_description={'Net Sales (Dry)','Net Sales (Fresh)'}>}
  aggr(
    $(vBudget_Forecast),
    account_company_key,
    account_pl_category_description
  )
)

YTD下的预算GP逻辑（存在问题） #

预算GP（基础）vBudget_GP #

SUM(
  {
    <
      trx_type={'sales_forecast'},
      MONTH_NUM={$(=Max(MONTH_NUM))}
    >
  }
  quantity_original_uom
  * component_ytd_unit_price
  * ( ($(vGPI($(=Max(MONTH_NUM)))) ) / 100 )
)

预算GP-冷藏vBudget_GP_Reefer #

sum(
  {<account_pl_category_description={"Net Sales (Reefer)","Net Sales (Arla)"}>}
  aggr(
    $(vBudget_GP),
    account_company_key,
    account_pl_category_description
  )
)

预算GP-干货vBudget_GP_Dry #

sum(
  {<account_pl_category_description={'Net Sales (Dry)','Net Sales (Fresh)'}>}
  aggr(
    $(vBudget_GP),
    account_company_key,
    account_pl_category_description
  )
)

GPI百分比计算变量vGPI #

aggr(
  max(
    {
      <
        gpi_record_key={"*"},
        MONTH_NUM={"$(=$1)"}
      >
    }
    if(
      trx_date >= gpi_from_period
      and trx_date <= gpi_to_period,
      percent_gpi
    )
  ),
  company_key,
  customer_key,
  product_key,
  account_pl_category_description
)

问题详情 #

变量依赖Max(MONTH_NUM)，单月选择时运行正常，但转换为YTD（1月至所选月份）时：

• 预测YTD可修复
• 预算GP YTD计算错误
• 多选月份时GP值虚高

核心问题出在vGPI中的aggr() + max()逻辑：对YTD总额应用单一月份的GPI百分比，导致计算偏差。

解决方案 #

1. 月度GP转真实YTD的最佳实践 #

核心逻辑是按月计算GP后再求和，而非对YTD总额直接应用单一GPI。具体步骤：

• 移除vGPI中固定月份的限制，让其匹配交易所属月份的GPI
• 重构基础GP变量，按月份维度聚合计算单月GP，再累加得到YTD值

2. 跨YTD正确应用GPI避免虚高的方法 #

修改vGPI变量

新增MONTH_NUM维度，确保每个月份的交易匹配对应月份的GPI：

aggr(
  max(
    {<gpi_record_key={"*"}>}
    if(
      trx_date >= gpi_from_period and trx_date <= gpi_to_period,
      percent_gpi
    )
  ),
  company_key, customer_key, product_key, account_pl_category_description, MONTH_NUM
)

重构YTD基础GP变量vBudget_GP_YTD

按月份维度计算单月GP后求和，确保每个月份的交易使用对应GPI：

SUM(
  {<trx_type={"sales_forecast"}>}
  aggr(
    sum(quantity_original_uom * component_ytd_unit_price * ($(vGPI)/100)),
    MONTH_NUM, company_key, customer_key, product_key, account_pl_category_description
  )
)

3. GP计算方式选择：按月计算后求和 #

必须选择按月计算后求和，因为GPI是随时间变化的，直接用YTD总额乘单一GPI会导致计算偏差。Qlik中通过保留MONTH_NUM维度的聚合实现，确保每个月份的独立计算后再累加。

对应YTD版本的冷藏/干货GP变量

预算GP-冷藏（YTD）vBudget_GP_Reefer_YTD

sum(
  {<account_pl_category_description={"Net Sales (Reefer)","Net Sales (Arla)"}>}
  aggr(
    $(vBudget_GP_YTD),
    account_company_key, account_pl_category_description, MONTH_NUM
  )
)

预算GP-干货（YTD）vBudget_GP_Dry_YTD

sum(
  {<account_pl_category_description={'Net Sales (Dry)','Net Sales (Fresh)'}>}
  aggr(
    $(vBudget_GP_YTD),
    account_company_key, account_pl_category_description, MONTH_NUM
  )
)

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